Skip to main content
Neon Postgres Docs
current

Search documentation

Type to search this documentation.

On this pageOverview

Postgres jsonarrayelements() 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.

Function signature

Section titled “Function signature”
SQL
json_array_elements(json)

json_array_elements example

Section titled “json_array_elements example”

Suppose you have a developers table with information about developers:

developers

SQL
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"]');
text
| 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:

SQL
SELECT id, name, skill
FROM developers,
    json_array_elements(skills) AS skill;

This query returns the following result:

text
| 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

Section titled “Advanced examples”

This section shows advanced json_array_elements examples.

json_array_elements with nested data

Section titled “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

SQL
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"]}');
text
| 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:

SQL
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:

text
| 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

Section titled “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:

SQL
SELECT *
FROM products
WHERE 'Blue' IN (
    SELECT json_array_elements_text(details->'colors')
);

This query returns the following values:

text
| 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

Section titled “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

SQL
INSERT INTO products (id, name, details) VALUES (6, 'Socks', '{"sizes": ["S", null, "L", "XL"], "colors": ["White", "Black", "Gray"]}');
text
| 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:

SQL
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:

text
| id | name  | size |
|----|-------|------|
|  6 | Socks | "S"  |
|  6 | Socks | null |
|  6 | Socks | "L"  |
|  6 | Socks | "XL" |

Nested arrays in json_array_elements

Section titled “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

SQL
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"]}]}');
text
| 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:

SQL
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:

text
| 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

Section titled “Additional considerations”

This section outlines additional considerations including alternative functions and JSON array order.

Alternates to json_array_elements

Section titled “Alternates to json_array_elements”
  • jsonb_array_elements - Consider this variant for performance benefits with jsonb data. jsonb_array_elements only accepts jsonb data, while json_array_elements works with both json and jsonb. It is typically faster, especially for larger arrays, due to its optimization for the binary jsonb format.
  • json_array_elements_text - While json_array_elements returns each extracted element as a JSON value, json_array_elements_text returns each extracted element as a plain text string.

Ordering json_array_elements output using WITH ORDINALITY

Section titled “Ordering json_array_elements output using WITH ORDINALITY”

If the order of the elements is important, consider using the WITH ORDINALITY option:

SQL
SELECT
   id,
   name,
   skill,
   ordinality
FROM
   developers,
   json_array_elements(skills) WITH ORDINALITY AS t(skill, ordinality);

This query returns the following values:

text
| 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.

Resources

Section titled “Resources”
Suggest an edit

Propose a replacement for this page. The site team reviews it before applying any changes.

Export
Documentation menu