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 subscript does not exist, NULL is returned. | SELECT '[{"a":"foo"}, {"b":"bar"}, {"c":"baz"}]'::json->2;
?column?
-------------
{"c":"baz"}
(1 row)
|
-> | object-json(b) | text | json(b) | Obtains the value by a key. If no record exists, NULL is returned. | SELECT '{"a":{"b":"foo"}}'::json->'a';
?column?
-------------
{"b":"foo"}
(1 row)
|
->> | Array-json(b) | int | text | Obtains the array-json element. If the subscript does not exist, NULL is returned. | SELECT '[{"a":"foo"}, {"b":"bar"}, {"c":"baz"}]'::json->>2;
?column?
-------------
{"c":"baz"}
(1 row)
|
->> | object-json(b) | text | text | Obtains the value by a key. If no record exists, NULL is returned. | SELECT '{"a":{"b":"foo"}}'::json->>'a';
?column?
-------------
{"b":"foo"}
(1 row)
|
#> | container-json (b) | text[] | json | Obtains the JSON object in the specified path. If the path does not exist, NULL is returned. Note A GaussDB(DWS) object identifier can end with a number sign (#). To avoid ambiguity during the parsing of a#>b, you need to add spaces in front of and behind the operator #>. Otherwise, a parsing error is reported. | SELECT '{"a":{"b":{"c":1}}}'::json #> '{a, b}';
?column?
----------
{"c":1}
(1 row)
|
#>> | container-json (b) | text[] | text | Obtains the JSON object in the specified path. If the path does not exist, NULL is returned. | SELECT '{"a":{"b":{"c":1}}}'::json #>> '{a, b}';
?column?
----------
{"c":1}
(1 row)
|
Operator | Right Operand Type | Return Type | Description | Example |
|---|---|---|---|---|
= | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":1}}}'::jsonb = '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
t
(1 row)
|
<> | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":1}}}'::jsonb <> '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
f
(1 row)
|
< | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":2}}}'::jsonb < '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
f
(1 row)
|
> | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":2}}}'::jsonb > '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
t
(1 row)
|
<= | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":2}}}'::jsonb <= '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
f
(1 row)
|
>= | jsonb | bool | Same as the jsonb_eq function, which compares the size of two jsonb files. | SELECT '{"a":{"b":{"c":2}}}'::jsonb >= '{"a":{"b":{"c":1}}}'::jsonb;
?column?
----------
t
(1 row)
|
? | text | bool | Whether the string of the key or element exists at the top layer of the JSON value. | SELECT '{"a":1, "b":2}'::jsonb ? 'b';
?column?
----------
t
(1 row)
|
?| | text[] | bool | Whether any of these array strings exists as a top-layer key. | SELECT '{"a":1, "b":2, "c":3, "d":4}'::jsonb ?| '{a, b, e}'::text[];
?column?
----------
t
(1 row)
|
?& | text[] | bool | Whether all these array strings exist as top-layer keys. | SELECT '{"a":1, "b":2, "c":3, "d":4}'::jsonb ?& '{a, b, c}'::text[];
?column?
----------
t
(1 row)
|
<@ | jsonb | bool | Whether all items in the JSON file on the left exist at the top layer of the JSON file on the right. | SELECT '{"b":3}'::jsonb <@ '{"a":{"b":{"c":2}}, "b":3}'::jsonb;
?column?
----------
t
(1 row)
|
@> | jsonb | bool | Whether all items in the JSON file on the right exist at the top layer of the JSON file on the left. | SELECT '{"a":{"b":{"c":2}}, "b":3}'::jsonb @> '{"b":3}'::jsonb;
?column?
----------
t
(1 row)
|
|| | jsonb | jsonb | Combines two JSONB objects into one. | SELECT '{"a":1, "b":2}'::jsonb || '{"c":3, "d":4}'::jsonb;
?column?
----------------------------------
{"a": 1, "b": 2, "c": 3, "d": 4}
(1 row)
|
| text | jsonb | Deletes a jsonb object and the specified key-value pair. | SELECT '{"a":1, "b":2}'::jsonb - 'a';
?column?
----------
{"b": 2}
(1 row)
|
| text | jsonb | Deletes a jsonb object and the specified key-value pair. | SELECT '{"a":1, "b":2, "c":3, "d":4}'::jsonb - '{a, b}'::text[];
?column?
------------------
{"c": 3, "d": 4}
(1 row)
|
| int | jsonb | Deletes the element corresponding to the subscript in the JSONB array. | SELECT '["a", "b", "c"]'::jsonb - 2;
?column?
------------
["a", "b"]
(1 row)
|
#- | text[] | jsonb | Deletes the key-value pair corresponding to the path in the JSONB object. | SELECT '{"a":{"b":{"c":{"d":1}}}, "e":2, "f":3}'::jsonb #- '{a, b}'::text[];
?column?
---------------------------
{"a": {}, "e": 2, "f": 3}
(1 row)
|