Postgres operators for JSONB and array values — access, containment, existence, modification, and path queries — each with a runnable example and its result.
⌕
JSONB Access5 operators
->
Extract a JSON object field by key, or an array element by integer index. Returns jsonb. NULL if the key/index does not exist.
'{"a": {"b": 1}}'::jsonb -> 'a'
→ {"b": 1}
->
Array element by integer index (zero-based; negative indexes count from the end).
'[10, 20, 30]'::jsonb -> -1
→ 30
->>
Extract a JSON object field (or array element) as text. Strips the surrounding JSON quotes for string values.
'{"a": "hello"}'::jsonb ->> 'a'
→ hello
#>
Extract the JSON value at the given path. Path is a text[] of keys and/or array indexes. Returns jsonb.
'{"a": {"b": {"c": 1}}}'::jsonb #> '{a,b,c}'
→ 1
#>>
Extract the JSON value at the given path, returned as text. Strips JSON quotes from string values.
'{"a": {"b": "hi"}}'::jsonb #>> '{a,b}'
→ hi
JSONB Existence3 operators
?
Does the text exist as a top-level key in the JSON object, or as an element of the JSON array? (Only checks the top level — does not recurse.) GIN-indexable with jsonb_ops or jsonb_path_ops.
'{"a": 1, "b": 2}'::jsonb ? 'a'
→ true
?|
Do any of the strings in the text[] exist as top-level keys or array elements?
'{"a": 1, "b": 2}'::jsonb ?| array['c', 'b']
→ true
?&
Do all of the strings in the text[] exist as top-level keys or array elements?
'{"a": 1, "b": 2}'::jsonb ?& array['a', 'b']
→ true
JSONB Containment2 operators
@>
Does the left JSONB value contain the right one? An object contains another if it has all the same key/value pairs (recursively). An array contains another if every element of the right array appears in the left array. GIN-indexable.
'{"a": 1, "b": 2}'::jsonb @> '{"a": 1}'::jsonb
→ true
<@
Is the left JSONB value contained within the right? Mirror image of @>.
'{"a": 1}'::jsonb <@ '{"a": 1, "b": 2}'::jsonb
→ true
JSONB Modification4 operators
||
Concatenate two JSONB values. Objects are merged at the top level only — keys in the right operand overwrite keys in the left. Arrays are concatenated. Mixing object + scalar wraps both in an array.
'{"a": 1}'::jsonb || '{"b": 2}'::jsonb
→ {"a": 1, "b": 2}
-
Delete a key/value pair from a JSONB object (right operand is text), or delete an array element (right operand is an integer).
Delete the field or array element at the specified path. Path is a text[] of keys and/or array indexes.
'{"a": {"b": 1, "c": 2}}'::jsonb #- '{a,b}'
→ {"a": {"c": 2}}
JSONB Path2 operators
@?12+
Does the jsonpath query return any item for the JSON value? (Predicate that checks for existence of matches.)
'{"a": [1, 2, 3]}'::jsonb @? '$.a[*] ? (@ > 1)'
→ true
@@12+
Evaluate a jsonpath predicate against the JSON value. Returns true, false, or NULL (when the path returns no items or the result is not boolean).
'{"a": 2}'::jsonb @@ '$.a > 1'
→ true
Array Containment3 operators
@>
Does the left array contain the right one? True when every element of the right array appears somewhere in the left array (ignoring order and duplicates). GIN-indexable.
ARRAY[1, 2, 3] @> ARRAY[2, 1]
→ true
<@
Is the left array contained by the right one? Mirror image of @>.
ARRAY[2, 1] <@ ARRAY[1, 2, 3]
→ true
&&
Do the arrays overlap — that is, share at least one element in common? GIN-indexable.
ARRAY[1, 2, 3] && ARRAY[3, 4, 5]
→ true
Array Concatenation & Comparison5 operators
||
Concatenate two arrays into one. Multidimensional arrays must have compatible dimensions.
ARRAY[1, 2] || ARRAY[3, 4]
→ {1, 2, 3, 4}
||
Prepend or append a single element to an array. The element type must match the array's element type.
0 || ARRAY[1, 2]
→ {0, 1, 2}
=
Array equality. True when both arrays have the same dimensions and all corresponding elements are equal.
ARRAY[1, 2, 3] = ARRAY[1, 2, 3]
→ true
<>
Array inequality. The negation of =.
ARRAY[1, 2] <> ARRAY[1, 2, 3]
→ true
<, >, <=, >=
Lexicographic comparison of arrays. Compares element by element; the first unequal pair determines the result.