PostgreSQL jsonb_array_elements Function

The jsonb_array_elements() function in PostgreSQL expands a JSON array into a set of rows, with one row per array element. It's essential for processing JSON arrays, normalizing data, and performing analysis on array contents.

Syntax

-- Returns set of JSONB values (one per array element)
jsonb_array_elements(jsonb_array)

-- Text variant - returns set of TEXT values
jsonb_array_elements_text(jsonb_array)

-- JSON variant (less efficient)
json_array_elements(json_array)
json_array_elements_text(json_array)

Official Documentation

Example usage

-- Expand simple array
SELECT jsonb_array_elements('[1, 2, 3, 4, 5]'::JSONB);
-- Result: 5 rows with values 1, 2, 3, 4, 5

-- Expand array of strings
SELECT jsonb_array_elements('["apple", "banana", "orange"]'::JSONB);
-- Result: 3 rows with "apple", "banana", "orange" (as JSONB)

-- Use with text variant
SELECT jsonb_array_elements_text('["red", "green", "blue"]'::JSONB);
-- Result: 3 rows with red, green, blue (as TEXT)

-- Expand array from table column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags JSONB
);

SELECT
    id,
    name,
    jsonb_array_elements_text(tags) AS tag
FROM products;

-- Find all unique tags across products
SELECT DISTINCT jsonb_array_elements_text(tags) AS tag
FROM products
ORDER BY tag;

-- Count array elements
SELECT
    id,
    name,
    jsonb_array_length(tags) AS tag_count
FROM products;

-- Expand array of objects
SELECT
    value ->> 'name' AS person_name,
    value ->> 'age' AS person_age
FROM jsonb_array_elements('[
    {"name": "Alice", "age": 30},
    {"name": "Bob", "age": 25}
]'::JSONB);

-- Use with table containing array of objects
SELECT
    order_id,
    item ->> 'product' AS product,
    (item ->> 'quantity')::INT AS quantity,
    (item ->> 'price')::NUMERIC AS price
FROM orders,
     jsonb_array_elements(order_items) AS item;

-- Filter expanded array elements
SELECT
    product_id,
    tag
FROM products,
     jsonb_array_elements_text(tags) AS tag
WHERE tag LIKE '%electronic%';

-- Aggregate after expansion
SELECT
    tag,
    COUNT(*) AS product_count
FROM products,
     jsonb_array_elements_text(tags) AS tag
GROUP BY tag
ORDER BY product_count DESC;

-- Nested array expansion
SELECT
    user_id,
    category,
    item
FROM users,
     jsonb_each(preferences) AS prefs(category, items),
     jsonb_array_elements_text(items) AS item;

-- Find records with specific array element
SELECT * FROM products
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(tags) AS tag
    WHERE tag = 'featured'
);

-- Index on array contains (better performance)
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> '["featured"]'::JSONB;

-- Calculate sum from array of numbers
SELECT SUM((value::TEXT)::NUMERIC) AS total
FROM jsonb_array_elements('[10, 20, 30, 40]'::JSONB);

-- WITH ORDINALITY to get array position
SELECT
    ord,
    value::TEXT AS item
FROM jsonb_array_elements('["first", "second", "third"]'::JSONB)
     WITH ORDINALITY AS t(value, ord);

Common issues

1. Only for arrays: jsonb_array_elements only works with JSON arrays, not objects.
2. Function in FROM: Must be used in FROM clause or with LATERAL join.
3. Nested arrays: Doesn't automatically expand nested arrays; requires multiple calls.
4. Performance: Expanding large arrays can be expensive; consider GIN indexes for containment queries.

Best practices

1. Use jsonb_array_elements for JSONB (more efficient than json_array_elements).
2. Use jsonb_array_elements_text when you need text values directly.
3. For simple containment checks, use @> operator with GIN index instead of expanding.
4. Use WITH ORDINALITY when you need array element positions.

Frequently Asked Questions

Q: What's the difference between jsonb_array_elements and jsonb_array_elements_text?
A: jsonb_array_elements returns JSONB values (preserving structure), while jsonb_array_elements_text returns TEXT values. Use the text variant for simple values when you don't need JSON structure.

Q: How do I get both the array index and value?
A: Use WITH ORDINALITY: SELECT ord, value FROM jsonb_array_elements(array_col) WITH ORDINALITY AS t(value, ord). The ord column gives you the 1-based position.

Q: Can I use this with nested arrays?
A: Yes, but you need to call it multiple times: SELECT jsonb_array_elements(jsonb_array_elements('[[[1,2],[3,4]]]')). Or use recursive CTEs for arbitrary nesting.

Q: What happens if I pass a non-array to jsonb_array_elements?
A: It raises an error. Check the type first with jsonb_typeof or use a conditional: WHERE jsonb_typeof(column) = 'array'.

Q: Is there a better way to check if an array contains a value?
A: Yes, use the @> containment operator with a GIN index: WHERE tags @> '["value"]'::JSONB. This is much faster than expanding the array.