I asked Gemini for some examples of using JSON_TABLE in Postgres 17.

---

Suhas

Hey Gemini, can you explain the usage of JSON_TABLE in Postgres 17 to me with a few simple examples? Consider that I have a wide table with a JSONB column. Make sure you cover a case with JOINs.

---

Gemini 1.5 Pro

JSON_TABLE in Postgres 17: Unpacking JSONB Data

The JSON_TABLE function in Postgres 17 is a powerful tool for extracting data from JSON arrays and converting it into relational format. This allows you to easily query and join data stored within JSONB columns.

1. Basic Example:

Let’s imagine you have a table named products with a JSONB column called attributes:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
  ('T-Shirt', '{"sizes": ["S", "M", "L"], "colors": ["red", "blue"]}'),
  ('Jeans', '{"sizes": ["XS", "M", "XL"], "colors": ["black", "blue"]}'),
  ('Hat', '{"sizes": ["One Size"], "colors": ["green", "yellow", "black"]}');

Now, you can use JSON_TABLE to extract the sizes for each product:

SELECT
  p.name,
  jt.size
FROM
  products p,
  JSON_TABLE(p.attributes, '$.sizes[*]' COLUMNS (
    size TEXT PATH '$'
  )) AS jt;

Explanation:

1. We select data from the products table (p) and use JSON_TABLE on the attributes column.
2. '$.sizes[*]' specifies the path within the JSON to extract data from. The [*] wildcard selects all array elements.
3. COLUMNS defines the output columns, in this case a single column named size of type TEXT with data extracted from the path $ (representing the current array element).
4. We alias the resulting table as jt for easier access.

This query will output a table with product names and their corresponding sizes:

name	size
T-Shirt	S
T-Shirt	M
T-Shirt	L
Jeans	XS
Jeans	M
Jeans	XL
Hat	One Size

2. Example with JOIN:

Let’s say you have another table called orders with a product_id column:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product_id INT REFERENCES products(id),
  quantity INT
);

INSERT INTO orders (product_id, quantity) VALUES
  (1, 2),
  (2, 1),
  (3, 3);

You can now use JSON_TABLE to join the orders table with the extracted sizes from the products table:

SELECT
  o.id AS order_id,
  p.name AS product_name,
  jt.size AS product_size,
  o.quantity
FROM
  orders o
JOIN
  products p ON o.product_id = p.id,
  JSON_TABLE(p.attributes, '$.sizes[*]' COLUMNS (
    size TEXT PATH '$'
  )) AS jt;

Explanation:

1. We join the orders and products tables on the product_id.
2. We use JSON_TABLE within the FROM clause to extract the sizes for each product, similar to the previous example.
3. The resulting table from JSON_TABLE is then implicitly joined with the other tables based on the shared product_id.

This query will output a table with order details, including the size of each ordered product:

order_id	product_name	product_size	quantity
1	T-Shirt	S	2
1	T-Shirt	M	2
1	T-Shirt	L	2
2	Jeans	XS	1
2	Jeans	M	1
2	Jeans	XL	1
3	Hat	One Size	3

These examples showcase the basic usage of JSON_TABLE and how it can be used with joins to effectively query and analyze data stored within JSONB columns in Postgres 17. Remember to adjust the JSON path and output columns based on your specific data structure and desired results.

---

Suhas

Give me more examples

---

Gemini 1.5 Pro

Alright, here are some more advanced examples of using JSON_TABLE in Postgres 17 with different scenarios:

3. Filtering with WHERE clause:

Let’s say you want to find all products that come in the color blue. You can filter the results of JSON_TABLE using a WHERE clause:

SELECT
  p.name,
  jt.color
FROM
  products p,
  JSON_TABLE(p.attributes, '$.colors[*]' COLUMNS (
    color TEXT PATH '$'
  )) AS jt
WHERE
  jt.color = 'blue';

This query will only return rows where the extracted color value is “blue”:

name	color
T-Shirt	blue
Jeans	blue

4. Extracting multiple values from nested objects:

Imagine your attributes JSONB column contains nested objects like this:

{ "sizes": ["S", "M"], "details": { "material": "cotton", "made_in": "China" } }

You can extract values from both the nested details object and the top-level array:

SELECT
  p.name,
  jt.size,
  jt.material,
  jt.made_in
FROM
  products p,
  JSON_TABLE(
    p.attributes,
    '$' COLUMNS (
      size TEXT PATH '$.sizes[0]', -- Extract first size only
      material TEXT PATH '$.details.material',
      made_in TEXT PATH '$.details.made_in'
    )
  ) AS jt;

This will output a table with product names, the first size in the array, material, and country of manufacture.

5. Using LATERAL join for complex relationships:

LATERAL joins can be used with JSON_TABLE for more complex scenarios. Let’s say you have a table promotions with a JSONB column rules containing product IDs and their discounts:

CREATE TABLE promotions (
  id SERIAL PRIMARY KEY,
  name TEXT,
  rules JSONB
);

INSERT INTO promotions (name, rules) VALUES
  ('Summer Sale', '{"products": [{"id": 1, "discount": 0.2}, {"id": 3, "discount": 0.1}]}');

You can use a LATERAL join to find the discount for each product in the orders table:

SELECT
  o.id AS order_id,
  p.name AS product_name,
  jt.discount
FROM
  orders o
JOIN
  products p ON o.product_id = p.id
LEFT JOIN LATERAL
  JSON_TABLE(
    (SELECT rules FROM promotions WHERE name = 'Summer Sale'),
    '$.products[*]' COLUMNS (
      product_id INT PATH '$.id',
      discount DECIMAL PATH '$.discount'
    )
  ) AS jt ON p.id = jt.product_id;

This query uses a subquery to find the relevant promotion, then uses LATERAL join with JSON_TABLE to extract discounts for matching product IDs from the rules JSONB column.

These examples demonstrate more advanced usage of JSON_TABLE for querying and joining data within JSONB columns in Postgres 17. You can combine these techniques with various clauses and functions to efficiently extract and analyze your JSON data.