PostgreSQL – JSON_ARRAY_ELEMENTS Function
The json_array_elements function is used to expand a JSON array into a set of rows. This function takes a JSON array as input and returns a set of rows, where each row represents an element from the input array. This is particularly useful when you want to work with individual elements of a JSON array within a SQL query.
The basic syntax of the json_array_elements function is as shown below:
SELECT * FROM json_array_elements(json_array) AS alias(element);where json_array is the JSON array expression that you want to expand.
and alias is an optional alias for the generated table that holds the expanded elements.
and element is the name you give to the column that will hold the individual elements of the JSON array.
Here’s an example to illustrate its usage. Let’s say you have a table orders with a JSON column products containing an array of product names for each order:
CREATE TABLE orders ( id serial PRIMARY KEY, order_data jsonb ); INSERT INTO orders (order_data)VALUES ('{"products": ["item1", "item2", "item3"]}'), ('{"products": ["item4", "item5"]}'), ('{"products": ["item6"]}');
SELECT id, element AS product FROM orders, json_array_elements(order_data->'products') AS products(element);In this example, the json_array_elements function is used to expand the "products" array within each order, resulting in a set of rows where each row contains an id from the orders table and an individual product element from the expanded JSON array.
The json_array_elements function works with JSON arrays. If you’re using JSONB data type, you can access the JSON array using the -> operator, as shown in the above example. If you’re using the JSON data type, you’ll use the ->> operator to access the JSON array and then apply the json_array_elements function.