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.
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.
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. |
/ |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot