Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ Functions and Operators/ JSON/JSONB Functions and Operators/ JSON/JSONB Functions and Operators
Updated on 2025-08-19 GMT+08:00
View PDF

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.

/