JSON/JSONB Functions and Operators

For details about the JSON/JSONB data type, see JSON/JSONB Types. Related operators are described in Table 1 and Table 2.

**Table 1** Common JSON/JSONB operators
Operator	Left Operand Type	Right Operand Type	Return Type	Description	Example
->	Array-json(b)	int	json(b)	Obtains the array-json element. If the index is not found, NULL will be returned.	SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; ?column? ------------- {"c":"baz"} (1 row)
->	object-json(b)	text	json(b)	Obtains the value of a key. If no value is found, NULL will be returned.	SELECT '{"a": {"b":"foo"}}'::json->'a'; ?column? ------------- {"b":"foo"} (1 row)
->>	Array-json(b)	int	text	Obtains JSON array elements. If the index is not found, NULL will be returned.	SELECT '[1,2,3]'::json->>2; ?column? ---------- 3 (1 row)
->>	object-json(b)	text	text	Obtains the value of a key. If no value is found, NULL will be returned.	SELECT '{"a":1,"b":2}'::json->>'b'; ?column? ---------- 2 (1 row)
#>	container-json (b)	text[]	json(b)	Obtains the JSON object in the specified path. If the path is not found, null will be returned.	SELECT '{"a": {"b":{"c": "foo"}}}'::json #>'{a,b}'; ?column? -------------- {"c": "foo"} (1 row)
#>>	container-json (b)	text[]	text	Obtains the JSON object in the specified path. If the path is not found, null will be returned.	SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json #>>'{a,2}'; ?column? ---------- 3 (1 row)

For the #> and #>> operators, if no data is found in the specified path, the function will return null instead of reporting an error.

**Table 2** Additional JSONB support for operators
Operator	Right Operand Type	Description	Example
@>	jsonb	Specifies whether the top layer of the left JSON contains all items at the top layer of the right JSON.	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@	jsonb	Specifies whether all items of the left JSON are present at the top layer of the right JSON.	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?	text	Specifies whether the string of the key or element is present at the top layer of the JSON value.	'{"a":1, "b":2}'::jsonb ? 'b'
?\|	text[]	Specifies whether any of these array strings is present as a top-layer key.	'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']
?&	text[]	Specifies whether all these array strings are present as top-layer keys.	'["a", "b"]'::jsonb ?& array['a', 'b']
=	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_eq function.	/
<>	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_ne function.	/
<	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_lt function.	/
>	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_gt function.	/
<=	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_le function.	/
>=	jsonb	Determines the size between two JSONB files, which is the same as the jsonb_ge function.	/