Your previews will never be the same: Create database branches with anonymized PII in seconds.
Neon
12.9kLog InSign Up
Postgres guides/Functions

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.

Sign Up

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

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.

Resources

Last updated on

Previousdense_rank
Nextjsonb_array_elements
Edit this page
Was this page helpful?