Querying Arrays Inside JSONB With jsonb_array_elements

A JSON array in a column looks like something SQL should handle easily, and it half does. Testing whether it contains a value is a one-liner. Counting its elements is a function. But the moment you want to filter elements, join them to another table or change one of them, you have to turn the array into rows and back, and there are several ways to do it with different costs. This article covers the common operations on arrays inside JSONB, with the outputs from PostgreSQL 18, and a measurement of five ways to answer the question "which rows have this tag".

Does the array contain this value

For an array of scalars, four forms answer the question:

select count(*) from products where attrs @> '{"tags":["gift"]}';           -- containment
select count(*) from products where attrs -> 'tags' ? 'gift';                -- existence
select count(*) from products where attrs @? '$.tags[*] ? (@ == "gift")';    -- jsonpath
select count(*) from products
 where exists (select 1 from jsonb_array_elements_text(attrs -> 'tags') t where t = 'gift');

All four returned 66,666 rows. Their timings on the 200,000-row table, with no index serving the question except where noted:

form                                             time
attrs @? '$.tags[*] ? (@ == "gift")'              32 ms
attrs -> 'tags' ? 'gift'                          60 ms
attrs @> '{"tags":["gift"]}'                      83 ms
'gift' = any (array(select jsonb_array_elements_text(...)))   176 ms
exists (select ... jsonb_array_elements_text ...) 204 ms

The two that unnest the array and test each element are the slowest by a wide margin, because they start a subquery for every row. The operator forms stay inside one function call per row. Use unnesting when you need the elements themselves, not to test for membership. And remember that at a third of the table, none of them is a selective query; only the containment form can use the GIN index on attrs, and with the selectivity of this question the planner may reasonably prefer a scan.

Some details about containment on arrays are worth keeping in your head:

• Order and duplicates are ignored. '["a","b"]'::jsonb @> '["b"]' is true, and so is @> '["b","b"]'.

• A scalar on the right is treated as a one-element array. '["a","b"]'::jsonb @> '"a"' returned true. This is a documented special case, and it surprises people who expect a type mismatch.

• Nesting matters. '[1,2,[3,4]]'::jsonb @> '[3]' is false, because 3 is not a top-level element, while @> '[[3]]' is true.

• **The ? operator tests strings only.** On an array it checks for a string element equal to the key; it does not look at numbers or objects, and '[{"a":1}]'::jsonb ? 'a' is false.

• **?& and ?|** test all or any of several strings. On the sample data, attrs -> 'tags' ?| array['gift','eco'] matched 166,667 rows, while ?& for the same two matched none, which agreed with the containment form @> '{"tags":["gift","eco"]}', also zero, since the generated tag lists never held both.

Arrays of objects

When elements are objects, containment takes a partial object:

select '[{"sku":"A","q":1},{"sku":"B","q":2}]'::jsonb @> '[{"sku":"B"}]';  -- true
select '[{"sku":"A","q":1}]'::jsonb @> '[{"sku":"A","q":2}]';                -- false

Each object on the right must be contained in some object on the left, so one element has to satisfy all of the keys you list. That makes it the right tool for "an item with sku B", and the wrong one for "an item with quantity above 3". For comparisons, use jsonpath: doc @? '$.items[*] ? (@.qty >= 5)' found order 2 in my sample, and doc @@ '$.items[*].qty > 4' returns true for the same row.

Counting and positions

jsonb_array_length(x) gives the number of elements: 2, 1 and 0 for the three sample orders. It raises an error on anything that is not an array: jsonb_array_length('{"a":1}') failed with cannot get array length of a non-array, and on a JSON null with cannot get array length of a scalar. A missing key gives SQL NULL, which passes through. If a column can hold either, guard with case when jsonb_typeof(doc -> 'items') = 'array' then jsonb_array_length(doc -> 'items') else 0 end, or use the jsonpath method that does not fail: jsonb_path_query_first(doc, '$.items.size()').

To read one position, use -> with an integer, from the start or from the end with a negative number: '["a","b","c"]'::jsonb -> 1 is "b", and ->> -1 is c. The positions of elements as they come out of a query come from with ordinality:

select e.elem, e.pos
from jsonb_array_elements('["x","y","z"]') with ordinality as e(elem, pos);

The positions are numbered from 1, unlike the zero-based -> index, which is a source of off-by-one errors when you mix them.

Unnesting

jsonb_array_elements(arr) returns a row per element as jsonb, and jsonb_array_elements_text returns each as text. Join it to its parent with a lateral reference:

select o.id, e ->> 'sku' as sku, (e ->> 'qty')::int as qty
from orders o
cross join lateral jsonb_array_elements(o.doc -> 'items') e;

An order with an empty array yields no rows from the cross join and vanishes from the output. Use left join lateral (...) on true to keep it with null item columns. On a JSON null or on an object, the function raises an error (cannot extract elements from an object), which is a reminder that unnesting expects clean data. Guard with where jsonb_typeof(doc -> 'items') = 'array' in a subquery if the shape varies.

From here you can aggregate normally: sum((e ->> 'qty')::int * (e ->> 'price')::numeric) grouped by sku gave 7 units and 66.5 for A1, and 1 and 20 for B2 across the three orders. When you need typed columns for several fields at once, the next step is expanding JSONB into rows with jsonb_to_recordset, and from PostgreSQL 17 JSON_TABLE.

Changing and rebuilding arrays

Arrays have no in-place edit by value. You can append (tags || '"new"'), remove by value or position (tags - 'eco', tags - 0), set by position (jsonb_set(doc, '{tags,1}', '"x"')) and insert at a position (jsonb_insert), all covered in adding and removing keys from JSONB. For anything conditional, take the array apart, change the elements and put it back with jsonb_agg:

select (select jsonb_agg(case when (e ->> 'a')::int = 2 then e || '{"x": 1}' else e end)
        from jsonb_array_elements('[{"a":1},{"a":2}]') e);

returned [{"a": 1}, {"a": 2, "x": 1}]. Filtering is the same with a where in the subquery: keeping the even numbers of [1,2,3,4] gave [2, 4]. Two cautions. jsonb_agg over zero rows returns NULL, not [], so a filter that removes everything turns the array into SQL NULL unless you coalesce(..., '[]'). And the order of a plain jsonb_agg follows the order the subquery returns rows, which is the array order in practice when you unnest a single array, but add with ordinality and order by pos if the order must be guaranteed.

To remove duplicates: select jsonb_agg(distinct e) from jsonb_array_elements('["b","a","b","c"]') e returned ["a", "b", "c"], sorted as a side effect of distinct.

Between JSONB arrays and PostgreSQL arrays

Native arrays (text[]) are a different thing with different operators and a type system. To convert, array(select jsonb_array_elements_text(attrs -> 'tags')) produced {eco,bulk,new} for row 2, and to_jsonb(array['a','b']) produced ["a", "b"] going the other way. If you find yourself converting constantly, the column wants to be text[] with a GIN index, which has simpler operators (@>, &&) and stores less. That is a fair design for a flat list of tags; JSONB is the better home when the array sits inside a larger document that varies.

Indexing array lookups

Containment against an array can use a GIN index on the column or, more cheaply, on the array itself: create index on products using gin ((attrs -> 'tags')), which was 544 kB in indexing JSONB with GIN and when it helps. The query must use the same expression: attrs -> 'tags' @> '["gift"]'. The ? form can use that index too, if it is the default class. Unnesting queries cannot use any index; they read every document. For a lookup like "all orders containing sku B2" on a big table, containment with a GIN index is the one that stays fast.

When the array should be rows

An array in a document is convenient while it stays small and is read with its parent. It is the wrong shape when:

You query the elements on their own. If reports are "revenue by sku" or "top customers by item", you are unnesting millions of arrays every time. A child table order_items(order_id, sku, qty, price) is indexed, joined and aggregated without any of the machinery above.

Elements are referenced elsewhere. A foreign key from another table to an element inside an array is impossible. The element needs an identity and a row.

The array grows without bound. Appending one element to a 5,000 element array rewrites the whole document. Events, log entries and comments belong in rows.

You need to update elements independently. Two requests changing different elements of the same array both rewrite the full document, and with jsonb_set by position, one can overwrite the other if the order has shifted. Rows have row locks.

For a quick experiment with an array before you write a query, the JSON formatter and the JSONPath tester both run in your browser. The functions are all in the PostgreSQL manual, JSON processing functions, and the containment rules for arrays are explained under JSONB containment and existence.