Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ JSON/JSONB Functions and Operators/ JSON/JSONB Operators
Updated on 2024-09-02 GMT+08:00
View PDF
Share

JSON/JSONB Operators

Table 1 Common JSON and 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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
SELECT '{"a":{"b":{"c":1}}}'::json #>> '{a, b}';
 ?column?
----------
 {"c":1}
(1 row)
Table 2 Operators supported by jsonb

Operator

Right Operand Type

Return Type

Description

Example

=

jsonb

bool

Same as the jsonb_eq function, which compares the size of two jsonb files.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
SELECT '{"a":{"b":{"c":2}}, "b":3}'::jsonb @> '{"b":3}'::jsonb;
 ?column?
----------
 t
(1 row)

||

jsonb

jsonb

Combines two JSONB objects into one.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
SELECT '{"a":1, "b":2}'::jsonb - 'a';
 ?column?
----------
 {"b": 2}
(1 row)

-

text

jsonb

Deletes a jsonb object and the specified key-value pair.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
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.

 
1
2
3
4
5
 
SELECT '{"a":{"b":{"c":{"d":1}}}, "e":2, "f":3}'::jsonb #- '{a, b}'::text[];
         ?column?
---------------------------
 {"a": {}, "e": 2, "f": 3}
(1 row)