Postgres json_array_elements() function
Expand a JSON array into a set of rows
You can use json_array_elements function to expand a JSON array into a set of rows, each containing one element of the array. It is a simpler option compared to complex looping logic. It is also more efficient than executing the same operation on the application side by reducing data transfer and processing overhead.
Try it on Neon!
Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.
Function signature
json_array_elements(json)json_array_elements example
Suppose you have a developers table with information about developers:
developers
CREATE TABLE developers (
id INT PRIMARY KEY,
name TEXT,
skills JSON
);
INSERT INTO developers (id, name, skills) VALUES
(1, 'Alice', '["Java", "Python", "SQL"]'),
(2, 'Bob', '["C++", "JavaScript"]'),
(3, 'Charlie', '["HTML", "CSS", "React"]');| id | name | skills
|----|---------|---------------------------
| 1 | Alice | ["Java", "Python", "SQL"]
| 2 | Bob | ["C++", "JavaScript"]
| 3 | Charlie | ["HTML", "CSS", "React"]Now, let's say you want to extract a row for each skill from the skills JSON array. You can use json_array_elements to do that:
SELECT id, name, skill
FROM developers,
json_array_elements(skills) AS skill;This query returns the following result:
| id | name | skill |
|----|---------|--------------|
| 1 | Alice | "Java" |
| 1 | Alice | "Python" |
| 1 | Alice | "SQL" |
| 2 | Bob | "C++" |
| 2 | Bob | "JavaScript" |
| 3 | Charlie | "HTML" |
| 3 | Charlie | "CSS" |
| 3 | Charlie | "React" |Advanced examples
This section shows advanced json_array_elements examples.
json_array_elements with nested data
Let's consider a scenario where we have a products table storing information about products. The table schema and data are provided below.
products
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
details JSON
);
INSERT INTO products (id, name, details) VALUES
(1, 'T-Shirt', '{"sizes": ["S", "M", "L", "XL"], "colors": ["Red", "Blue", "Green"]}'),
(2, 'Hoodie', '{"sizes": ["XS", "S", "M", "L", "XL"], "colors": ["Black", "Gray"]}'),
(3, 'Dress', '{"sizes": ["S", "M", "L"], "colors": ["Pink", "Purple", "Black"]}'),
(4, 'Jeans', '{"sizes": ["28", "30", "32", "34"], "colors": ["Blue", "Black"]}'),
(5, 'Jacket', '{"sizes": ["S", "M", "L", "XL"], "colors": ["Black", "Brown", "Navy"]}');| id | name | details |
|----|---------|------------------------------------------------------------------------|
| 1 | T-Shirt | {"sizes": ["S", "M", "L", "XL"], "colors": ["Red", "Blue", "Green"]} |
| 2 | Hoodie | {"sizes": ["XS", "S", "M", "L", "XL"], "colors": ["Black", "Gray"]} |
| 3 | Dress | {"sizes": ["S", "M", "L"], "colors": ["Pink", "Purple", "Black"]} |
| 4 | Jeans | {"sizes": ["28", "30", "32", "34"], "colors": ["Blue", "Black"]} |
| 5 | Jacket | {"sizes": ["S", "M", "L", "XL"], "colors": ["Black", "Brown", "Navy"]} |The json_array_elements function can be used to get all the combinations of size and color for a specific product. For example:
SELECT
id,
name,
size,
color
FROM products AS p,
json_array_elements(p.details -> 'sizes') AS size,
json_array_elements(p.details -> 'colors') AS color
WHERE name = 'T-Shirt';This query returns the following values:
| id | name | size | color |
|----|---------|------|--------|
| 1 | T-Shirt | "S" | "Red" |
| 1 | T-Shirt | "S" | "Blue" |
| 1 | T-Shirt | "S" | "Green"|
| 1 | T-Shirt | "M" | "Red" |
| 1 | T-Shirt | "M" | "Blue" |
| 1 | T-Shirt | "M" | "Green"|
| 1 | T-Shirt | "L" | "Red" |
| 1 | T-Shirt | "L" | "Blue" |
| 1 | T-Shirt | "L" | "Green"|
| 1 | T-Shirt | "XL" | "Red" |
| 1 | T-Shirt | "XL" | "Blue" |
| 1 | T-Shirt | "XL" | "Green"|Filtering json_array_elements
You can use the json_array_elements function to extract the sizes from the JSON data and then filter the products based on a specific color (or size), as in this example:
SELECT *
FROM products
WHERE 'Blue' IN (
SELECT json_array_elements_text(details->'colors')
);This query returns the following values:
| id | name | details |
|----|----------|----------------------------------------------------------------------|
| 1 | T-Shirt | {"sizes": ["S", "M", "L", "XL"], "colors": ["Red", "Blue", "Green"]} |
| 4 | Jeans | {"sizes": ["28", "30", "32", "34"], "colors": ["Blue", "Black"]} |Handling NULL in json_array_elements
This example updates the table to insert another product (Socks) with one of the values in the sizes as null:
products
INSERT INTO products (id, name, details) VALUES (6, 'Socks', '{"sizes": ["S", null, "L", "XL"], "colors": ["White", "Black", "Gray"]}');| id | name | details |
|----|---------|-------------------------------------------------------------------------|
| 6 | Socks | {"sizes": ["S", null, "L", "XL"], "colors": ["White", "Black", "Gray"]} |Querying for Socks shows how null values in an array are handled:
SELECT
id,
name,
size
FROM products AS p,
json_array_elements(p.details -> 'sizes') AS size
WHERE name = 'Socks';This query returns the following values:
| id | name | size |
|----|-------|------|
| 6 | Socks | "S" |
| 6 | Socks | null |
| 6 | Socks | "L" |
| 6 | Socks | "XL" |Nested arrays in json_array_elements
You can also handle nested arrays with json_array_elements.
Consider a scenario where each product has multiple variants, and each variant has an array of sizes and an array of colors. This example uses an elecronics_products table, shown below.
electronics_products
CREATE TABLE electronics_products (
id INTEGER PRIMARY KEY,
name TEXT,
details JSON
);
INSERT INTO electronics_products (id, name, details) VALUES
(1, 'Laptop', '{"variants": [{"model": "A", "sizes": ["13 inch", "15 inch"], "colors": ["Silver", "Black"]}, {"model": "B", "sizes": ["15 inch", "17 inch"], "colors": ["Gray", "White"]}]}'),
(2, 'Smartphone', '{"variants": [{"model": "X", "sizes": ["5.5 inch", "6 inch"], "colors": ["Black", "Gold"]}, {"model": "Y", "sizes": ["6.2 inch", "6.7 inch"], "colors": ["Blue", "Red"]}]}');| id | name | details |
|----|------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | Laptop | {"variants": [{"model": "A", "sizes": ["13 inch", "15 inch"], "colors": ["Silver", "Black"]}, {"model": "B", "sizes": ["15 inch", "17 inch"], "colors": ["Gray", "White"]}]} |
| 2 | Smartphone | {"variants": [{"model": "X", "sizes": ["5.5 inch", "6 inch"], "colors": ["Black", "Gold"]}, {"model": "Y", "sizes": ["6.2 inch", "6.7 inch"], "colors": ["Blue", "Red"]}]} |To handle the nested arrays and extract information about each variant, you can use the json_array_elements function like this:
SELECT
id,
name,
variant->>'model' AS model,
size,
color
FROM
electronics_products,
json_array_elements(details->'variants') AS variant,
json_array_elements_text(variant->'sizes') AS t1(size),
json_array_elements_text(variant->'colors') AS t2(color);This query returns the following values:
| id | name | model | size | color |
|----|------------|-------|----------|--------|
| 1 | Laptop | A | 13 inch | Silver |
| 1 | Laptop | A | 13 inch | Black |
| 1 | Laptop | A | 15 inch | Silver |
| 1 | Laptop | A | 15 inch | Black |
| 1 | Laptop | B | 15 inch | Gray |
| 1 | Laptop | B | 15 inch | White |
| 1 | Laptop | B | 17 inch | Gray |
| 1 | Laptop | B | 17 inch | White |
| 2 | Smartphone | X | 5.5 inch | Black |
| 2 | Smartphone | X | 5.5 inch | Gold |
| 2 | Smartphone | X | 6 inch | Black |
| 2 | Smartphone | X | 6 inch | Gold |
| 2 | Smartphone | Y | 6.2 inch | Blue |
| 2 | Smartphone | Y | 6.2 inch | Red |
| 2 | Smartphone | Y | 6.7 inch | Blue |
| 2 | Smartphone | Y | 6.7 inch | Red |Additional considerations
This section outlines additional considerations including alternative functions and JSON array order.
Alternates to json_array_elements
jsonb_array_elements- Consider this variant for performance benefits withjsonbdata.jsonb_array_elementsonly acceptsjsonbdata, whilejson_array_elementsworks with bothjsonandjsonb. It is typically faster, especially for larger arrays, due to its optimization for the binaryjsonbformat.json_array_elements_text- Whilejson_array_elementsreturns each extracted element as aJSONvalue,json_array_elements_textreturns each extracted element as a plain text string.
Ordering json_array_elements output using WITH ORDINALITY
If the order of the elements is important, consider using the WITH ORDINALITY option:
SELECT
id,
name,
skill,
ordinality
FROM
developers,
json_array_elements(skills) WITH ORDINALITY AS t(skill, ordinality);This query returns the following values:
| id | name | skill | ordinality |
|----|---------|--------------|------------|
| 1 | Alice | "Java" | 1 |
| 1 | Alice | "Python" | 2 |
| 1 | Alice | "SQL" | 3 |
| 2 | Bob | "C++" | 1 |
| 2 | Bob | "JavaScript" | 2 |
| 3 | Charlie | "HTML" | 1 |
| 3 | Charlie | "CSS" | 2 |
| 3 | Charlie | "React" | 3 |The WITH ORDINALITY option in the query adds an ordinality column representing the original order of the skills in the array.