On this page

Show all

Help Center/ GaussDB(DWS)/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ JSON/JSONB Functions and Operators/ JSON/JSONB Operators

JSON/JSONB Operators

Updated on 2024-09-30 GMT+08:00

View PDF

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.

        
           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)

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.

        
           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)

Parent topic: JSON/JSONB Functions and Operators

Previous topic: JSON/JSONB Functions and Operators

Next topic: JSON/JSONB Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback