Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ Functions and Operators/ JSON/JSONB Functions and Operators

Updated on 2024-08-20 GMT+08:00

View PDF

JSON/JSONB Functions and Operators

For details about the JSON/JSONB data type, see JSON/JSONB Types. For details about the operator information, see Table 1 and Table 2.

**Table 1** JSON/JSONB common operators
Operators	Left Operand Type	Right Operand Type	Return Type	Description	Example
->	Array-json(b)	int	json(b)	Obtains the array-json element. If the index 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 is found, NULL is returned.	SELECT '{"a": {"b":"foo"}}'::json->'a'; ?column? ------------- {"b":"foo"} (1 row)
->>	Array-json(b)	int	text	Obtains the JSON array element. If the index does not exist, NULL is returned.	SELECT '[1,2,3]'::json->>2; ?column? ---------- 3 (1 row)
->>	object-json(b)	text	text	Obtains the value by a key. If no record is found, NULL is 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 does not exist, NULL is 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 does not exist, NULL is returned.	SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json #>>'{a,2}'; ?column? ---------- 3 (1 row)

For the #> and #>> operators, if no data can be found in the specified path, no error is reported and a NULL value is returned.

**Table 2** Additional JSONB support for operators
Operators	Right Operand Type	Description	Example
@>	jsonb	Specifies whether the top layer of the JSON on the left contains all items of the top layer of the JSON on the right.	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@	jsonb	Specifies whether all items in the JSON file on the left exist at the top layer of the JSON file on the right.	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?	text	Specifies whether the string of the key or element exists at the top layer of the JSON value.	'{"a":1, "b":2}'::jsonb ? 'b'
?\|	text[]	Specifies whether any of these array strings exists as top-layer keys.	'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']
?&	text[]	Specifies whether all these array strings exist 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.	/

Functions Supported by JSON/JSONB

array_to_json(anyarray [, pretty_bool])
Description: Returns the array as JSON. It combines a multi-dimensional array into a JSON array. Line feeds will be added between one-dimensional elements if pretty_bool is true.

Return type: json
Example:
```
gaussdb=# SELECT array_to_json('{{1,5},{99,100}}'::int[]);
    array_to_json
    ------------------
    [[1,5],[99,100]]
    (1 row)
```
row_to_json(record [, pretty_bool])
Description: Returns the row as JSON. Line feeds will be added between level-1 elements if pretty_bool is true.

Return type: json

Example:
```
gaussdb=# SELECT row_to_json(row(1,'foo'));
     row_to_json     
---------------------
 {"f1":1,"f2":"foo"}
(1 row)
```

json_array_element(array-json, integer), jsonb_array_element(array-jsonb, integer)
Description: Same as the operator `->`, which returns the element with the specified index in the array.

Return type: json, jsonb
Example:
```
gaussdb=# SELECT json_array_element('[1,true,[1,[2,3]],null]',2);
 json_array_element 
--------------------
 [1,[2,3]]
(1 row)
```

json_array_element_text(array-json, integer), jsonb_array_element_text(array-jsonb, integer)
Description: Same as the operator `->>`, which returns the element with the specified index in the array.

Return type: text, text

Example:
```
gaussdb=# SELECT json_array_element_text('[1,true,[1,[2,3]],null]',2);
json_array_element_text
-----------------------
  [1,[2,3]]
  (1 row)
```

json_object_field(object-json, text), jsonb_object_field(object-jsonb, text)
Description: Same as the operator `->`, which returns the value of a specified key in an object.

Return type: json, json
Example:
```
 gaussdb=# SELECT json_object_field('{"a": {"b":"foo"}}','a');
  json_object_field
  -------------------
  {"b":"foo"}
  (1 row)
```
json_object_field_text(object-json, text), jsonb_object_field_text(object-jsonb, text)
Description: Same as the operator `->`, which returns the value of a specified key in an object.

Return type: text, text
Example:
```
 gaussdb=# SELECT json_object_field_text('{"a": {"b":"foo"}}','a');
  json_object_field_text
  ----------------------
  {"b":"foo"}
  (1 row)
```

json_extract_path(json, VARIADIC text[]), jsonb_extract_path((jsonb, VARIADIC text[])
Description: Equivalent to the operator `#>` searches for JSON based on the path specified by $2 and returns the result.

Return type: json, jsonb
Example:
```
 gaussdb=# SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
   json_extract_path
  -------------------
   "stringy"
  (1 row)
```

json_extract_path_op(json, text[]), jsonb_extract_path_op(jsonb, text[])
Description: Same as the operator `#>`, searches for JSON based on the path specified by $2 and returns the result.

Return type: json, jsonb
Example:
```
  gaussdb=# SELECT json_extract_path_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
   json_extract_path_op
  ---------------------
   "stringy"
  (1 row)
```

json_extract_path_text(json, VARIADIC text[]), jsonb_extract_path_text((jsonb, VARIADIC text[])
Description: Equivalent to the operator `#>`, searches for JSON based on the path specified by $2 and return the result.

Return type: text, text
Example:
```
 gaussdb=# SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
   json_extract_path_text
  -----------------------
   stringy
  (1 row)
```

json_extract_path_text_op(json, text[]), jsonb_extract_path_text_op(jsonb, text[])
Description: Same as the operator `#>`, searches for JSON based on the path specified by $2 and return the result.

Return type: text, text
Example:
```
 gaussdb=# SELECT json_extract_path_text_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
   json_extract_path_text_op
  --------------------------
   stringy
  (1 row)
```

json_array_elements(array-json), jsonb_array_elements(array-jsonb)

Description: Splits an array. Each element returns a row.

Return type: json, jsonb

Example:

 gaussdb=# SELECT json_array_elements('[1,true,[1,[2,3]],null]');
   json_array_elements
  ---------------------
   1
   true
   [1,[2,3]]
   null
  (4 rows)

json_array_elements_text(array-json), jsonb_array_elements_text(array-jsonb)
Description: Splits an array. Each element returns a row.

Return type: text, text
Example:
```
gaussdb=# SELECT * FROM  json_array_elements_text('[1,true,[1,[2,3]],null]');
     value
  -----------
   1
   true
   [1,[2,3]]

  (4 rows)
```

json_array_length(array-json), jsonb_array_length(array-jsonb)

Description: Returns the array length.

Return type: integer

Example:

  gaussdb=# SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]');
   json_array_length
  -------------------
                   6
  (1 row)

json_each(object-json), jsonb_each(object-jsonb)
Description: Splits each key-value pair of an object into one row and two columns.

Return type: setof(key text, value json), setof(key text, value jsonb)
Example:
```
 gaussdb=# SELECT * FROM  json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
   key |  value
  -----+----------
   f1  | [1,2,3]
   f2  | {"f3":1}
   f4  | null
  (3 rows)
```

json_each_text(object-json), jsonb_each_text(object-jsonb)
Description: Splits each key-value pair of an object into one row and two columns.

Return type: setof(key text, value text), setof(key text, value text)
Example:
```
 gaussdb=# SELECT * FROM  json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
   key |  value
  -----+----------
   f1  | [1,2,3]
   f2  | {"f3":1}
   f4  |
  (3 rows)
```

json_object_keys(object-json), jsonb_object_keys(object-jsonb)

Description: Returns all keys at the top layer of the object.

Return type: SETOF text

Example:

  gaussdb=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
   json_object_keys
  ------------------
   f1
   f2
   f1
  (3 rows)

JSONB deduplication operations:

  gaussdb=# SELECT jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
   jsonb_object_keys
  -------------------
   f1
   f2
  (2 rows)

json_populate_record(anyelement, object-json [, bool]), jsonb_populate_record(anyelement, object-jsonb [, bool])

Description: $1 must be a compound parameter. Each key-value in the object-json file is split. The key is used as the column name to match the column name in $1 and fill in the $1 format.

Return type: anyelement, anyelement

Example:

  gaussdb=# CREATE TYPE jpop AS (a text, b int, c bool);
  CREATE TYPE
  gaussdb=# SELECT * FROM json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
     a    | b | c
  --------+---+---
   blurfl |   |
  (1 row)

gaussdb=# SELECT * FROM json_populate_record((1,1,null)::jpop,'{"a":"blurfl","x":43.2}');
     a    | b | c
  --------+---+---
   blurfl | 1 |
  (1 row)
gaussdb=# DROP TYPE jpop;
DROP TYPE

json_populate_record_set(anyelement, array-json [, bool]), jsonb_populate_record_set(anyelement, array-jsonb [, bool])
Description: Performs the preceding operations on each element in the $2 array by referring to the json_populate_record and jsonb_populate_record functions. Therefore, each element in the $2 array must be of the object-json type.

Return type: setof anyelement, setof anyelement
Example:
```
gaussdb=# CREATE TYPE jpop AS (a text, b int, c bool);
  CREATE TYPE
gaussdb=# SELECT * FROM json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]');
   a | b | c
  ---+---+---
   1 | 2 |
   3 | 4 |
  (2 rows)
gaussdb=# DROP TYPE jpop;
 DROP TYPE
```

json_typeof(json), jsonb_typeof(jsonb)

Description: Checks the JSON type.

Return type: text, text

Example:

 gaussdb=# SELECT value, json_typeof(value)  FROM (values (json '123.4'), (json '"foo"'), (json 'true'), (json 'null'), (json '[1, 2, 3]'), (json '{"x":"foo", "y":123}'), (NULL::json))  AS data(value);
          value         | json_typeof
  ----------------------+-------------
   123.4                | number
   "foo"                | string
   true                 | boolean
   null                 | null
   [1, 2, 3]            | array
   {"x":"foo", "y":123} | object
                        |
  (7 rows)

json_build_array( [VARIADIC "any"] )

Description: Constructs a JSON array from a variable parameter list.

Return type: array-json

Example:

 gaussdb=# SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}','');
                              json_build_array                               
-----------------------------------------------------------------------------
 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}, null]
(1 row)

json_build_object( [VARIADIC "any"] )
Description: Constructs a JSON object from a variable parameter list. The number of input parameters must be an even number. Every two input parameters form a key-value pair. Note that the value of a key cannot be null.

Return type: object-json
Example:
```
 gaussdb=# SELECT json_build_object(1,2);
   json_build_object
  -------------------
   {"1" : 2}
  (1 row)
```

jsonb_build_object( [VARIADIC "any"] )
Description: Constructs a JSONB object from a variable parameter list. The number of input parameters must be an even number. Every two input parameters form a key-value pair. Note that the key value cannot be NULL.

Return type: object-jsonb
- When an element in the variable parameter list contains an empty string (''), if the SQL compatibility mode of the database is A, the return result of the corresponding element is NULL; if the SQL compatibility mode of the database is PG, the return result of the corresponding element is an empty string. This is because empty strings are treated as NULL in A compatibility mode.
- When an element in the variable parameter list is of the DATE type, if the SQL compatibility mode of the database is A, the return result of the corresponding element contains hour, minute, and second; if the SQL compatibility mode of the database is PG, the return result of the corresponding element does not contain hour, minute, and second. This is because DATE will be replaced by TIMESTAMP(0) WITHOUT TIME ZONE in A compatibility mode.
Example:
```
gaussdb=# SELECT jsonb_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
                           jsonb_build_object
-------------------------------------------------------------------------
 {"a": 1, "b": 1.2, "c": true, "d": null, "e": {"x": 3, "y": [1, 2, 3]}}
(1 row)

gaussdb=# SELECT jsonb_build_object();
 jsonb_build_object
--------------------
 {}
(1 row)
```
json_to_record(object-json, bool), json_to_record(object-json)
Description: Like all functions that return record, the caller must explicitly define the structure of the record with an AS clause. The key-value pair of object-json is split and reassembled. The key is used as a column name to match and fill in the structure of the record specified by the AS clause. The bool input parameter of overload 1 specifies whether object nesting is allowed. The value true indicates yes and the value false indicates no. If there is only one input parameter, object nesting is allowed by default. If there is only one input parameter, the nested JSON object can be converted to the row type and arrays in square brackets can be parsed. Dates of the time type are equivalent to timestamp(0) in ORA compatibility mode. Compared with that in the PG compatibility mode, the value also displays the 00:00:00 (hour, minute, and second) character string.

Return type: record
Example:
```
gaussdb=# SELECT * FROM json_to_record('{"a":1,"b":"foo","c":"bar"}',true) AS x(a int, b text, d text);
   a |  b  | d
  ---+-----+---
   1 | foo |
  (1 row)
gaussdb=# SELECT * FROM json_to_record('{"a": {"x": 1, "y": 2},"b":"foo","c":[1, 2]}') AS x(a json, b text, c int[]);
          a         |  b  |   c   
  ------------------+-----+-------
   {"x": 1, "y": 2} | foo | {1,2}
  (1 row)
```

json_to_recordset(array-json, bool)
Description: Executes the preceding function on each element in the array by referring to the json_to_record function. Therefore, each element in the array must be object-json. For bool, refer to the json_to_record function. true indicates that nested objects can be parsed, and false indicates that nested objects cannot be parsed, that is, whether the value of an element in a JSON object can be a JSON object.

Return type: set of record
Example:
```
 gaussdb=# SELECT * FROM json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) AS x(a int, b text, c boolean);
   a |  b  | c
  ---+-----+---
   1 | foo |
   2 | bar | t
  (2 rows)
```

json_object(text[]), json_object(text[], text[])
Description: Constructs an object-json from a text array. This is an overloaded function. When the input parameter is a text array, the array length must be an even number, and members are considered as alternate key-value pairs. When two text arrays are used, the first array is considered as a key, and the second array a value. The lengths of the two arrays must be the same. Note that the value of a key cannot be null.

Return type: object-json
Example:
```
gaussdb=# SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
                        json_object
  -------------------------------------------------------
   {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
  (1 row)
```
```
  gaussdb=# SELECT json_object('{a,b,"a b c"}', '{a,1,1}');
                json_object
  ---------------------------------------
   {"a" : "a", "b" : "1", "a b c" : "1"}
  (1 row)
```

json_object([VARIADIC "any"] )
Description: Constructs a JSON object from a variable parameter list. The number of input parameters must be an even number. Every two input parameters form a key-value pair. If the key-value pair is null, an error is reported. If the parameter is in the odd format, an error is reported.

Parameter: variable parameter list. The input parameter is a combination of one or more key-value pairs.

Return type: json
Example:
```
gaussdb=# SELECT json_object('d',2,'c','name','b',true,'a',2,'a',NULL,'d',1);
               json_object                
------------------------------------------
 {"a": 2, "b": true, "c": "name", "d": 2}
(1 row)

gaussdb=# SELECT json_object('d',2,true,'name','b',true,'a',2,'aa', current_timestamp);
                                 json_object                                  
------------------------------------------------------------------------------
 {"1": "name", "a": 2, "b": true, "d": 2, "aa": "2023-08-12 11:28:13.385958"}
(1 row)
```
This function takes effect when b_format_version is set to '5.7' and b_format_dev_version is set to 's1' in the MySQL-compatible database. When this function takes effect, the original json_object behavior is replaced.

json_append/json_array_append(json, [VARIADIC "any"])

Description: Constructs several json_path-value pairs from a variable parameter list, adds a value to the path specified by json, and returns the modified JSON value. json_append is the same as json_array_append. If any parameter is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameter: The first input parameter must be in JSON format, and the second input parameter is a variable parameter list. The json_path-value pair is constructed from the variable parameter list. For details, see Table 3.

**Table 3** Parameters
Parameter	Type	Description	Value Range
json	json	JSON value to be modified.	The value must be in JSON format.
[VARIADIC "any"]	Variable any array	A variable parameter list, including variable json_path-value pairs.	json_path must be a valid path expression, and value can be any value.

Return type: json

Example:

gaussdb=# SELECT json_array_append('[1, [2, 3]]', '$[1]', 4, '$[0]', false, '$[0]', null, '$[0]', current_timestamp);
                       json_array_append                        
----------------------------------------------------------------
 [[1, false, null, "2023-08-12 14:27:16.142355+08"], [2, 3, 4]]
(1 row)

json_array([VARIADIC "any"])

Description: Constructs an array from a variable parameter list and returns a JSON array. If the function does not have any parameters, an empty JSON array is returned.

Parameter: The input parameter is a variable parameter list. The values in the list can be of any type.

Return type: json

Example:

-- If no input parameter is entered, an empty JSON array is returned.
gaussdb=# SELECT json_array();
 json_array 
------------
 []
(1 row)

-- The input parameter can be of any type.
gaussdb=# SELECT json_array(TRUE, FALSE, NULL, 114, 'text', current_timestamp);
                            json_array                             
-------------------------------------------------------------------
 [true, false, null, 114, "text", "2023-08-12 15:17:34.979294+08"]
(1 row)

json_array_insert(json, [VARIADIC "any"])

Description: Constructs one or more json_path-value pairs from a variable parameter list, inserts a value into the array path specified by the json_path in json, and returns a new JSON value. If there is already an existing value on the specified path, the value is inserted on the path and the existing value is moved backward. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 4 Parameters.

**Table 4** Parameters
Parameter	Type	Description	Value Range
json	json	JSON value to be modified.	The value must be in JSON format.
[VARIADIC "any"]	Variable any array	Variable parameter list, containing one or more json_path-value pairs.	json_path must be a valid path expression, and value can be any value.

Return type: json

Example:

-- Example of containing one json_path-value pair
gaussdb=# SELECT json_array_insert('[1, [2, 3]]', '$[1]', 4);
 json_array_insert 
-------------------
 [1, 4, [2, 3]]
(1 row)

-- Example of containing multiple json_path-value pairs
gaussdb=# SELECT json_array_insert('{"x": 1, "y": [1, 2]}', '$.y[0]', NULL, '$.y[0]', 123, '$.y[3]', current_timestamp);
                        json_array_insert                         
------------------------------------------------------------------
 {"x": 1, "y": [123, null, 1, "2023-08-14 14:54:12.85087+08", 2]}
(1 row)

json_contains(target_json, candidate_json[, json_path])

Description: The input parameters are two JSON objects and an optional path specified by json_path. If json_path is not specified, the system checks whether target_json contains candidate_json. If json_path is specified, the system checks whether candidate_json is contained in the JSON path specified by json_path in target_json. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 5.

**Table 5** Parameters
Parameter	Type	Description	Value Range
target_json	json	Target JSON object, which is used to check whether candidate_json is contained in the JSON object.	The value must be in JSON format.
candidate_json	json	JSON subobject, which is used to check whether it is contained in target_json.	The value must be in JSON format.
json_path	text	Specified JSON path. This parameter is optional. If this parameter is specified, the path specified by json_path in target_json is used to determine the inclusion relationship.	json_path must be a valid path expression.

Return type: bigint. If there is an inclusion relationship, 1 is returned. If there is no inclusion relationship, 0 is returned.

Example:

-- No path is specified.
gaussdb=# SELECT json_contains('[1, 2, {"x": 3}]', '{"x":3}');
 json_contains 
---------------
             1
(1 row)

-- A path is specified.
gaussdb=# SELECT json_contains('[1, 2, {"x": 3},[1,2,3,4]]', '2','$[1]');
 json_contains 
---------------
             1
(1 row)

gaussdb=# SELECT json_contains('[1, 2, {"x": 3},[1,2,3,4]]', '1','$[1]');
 json_contains 
---------------
             0
(1 row)

json_contains_path(json, mode_str, [VARIADIC text])

Description: Checks whether a JSON object has a value on the specified path. There can be multiple paths. The first input parameter is a JSON object, and the second input parameter can be one or all, specifying whether to check all paths. The third input parameter is a variable parameter list. All JSON paths are constructed from the variable parameter list. According to the mode, if a path value exists, 1 is returned. If no path value exists, 0 is returned. If json or json_path is null, null is returned. If the JSON format is incorrect, or json_path is an invalid path expression, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 6.

**Table 6** Parameters
Parameter	Type	Description	Value Range
json	json	JSON object to be transferred.	The value must be in JSON format.
mode_str	text	The value can be one or all. If the value is one and one path exists, 1 is returned; otherwise, 0 is returned. If the value is all and all paths exist, 1 is returned; otherwise, 0 is returned.	The value must be one or all, which is case-insensitive.
[VARIADIC text]	Variable text array	Variable parameter list from which all JSON paths are constructed.	json_path must be a valid path expression.

Return type: bigint

Example:

-- In the all scenario, all paths must exist.
gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'all', '$[0]', '$[1]', '$[2]');
 json_contains_path 
--------------------
                  1
(1 row)

gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'all', '$[0]', '$[1]', '$[6]');
 json_contains_path 
--------------------
                  0
(1 row)

-- In the one scenario, at least one path must exist.
gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'one', '$[0]', '$[1]', '$[5]');
 json_contains_path 
--------------------
                  1
(1 row)

json_depth(json)
Description: The input parameter is a JSON object. This function is used to return the maximum depth of the JSON object. If the input parameter is null, null is returned.

Parameter: The input parameter is a JSON string whose depth needs to be queried. If the input parameter is not in valid JSON format, an error is reported.

Return type: int
Example:
```
-- The depth of an empty array is 1.
gaussdb=# SELECT json_depth('[]');
 json_depth 
------------
          1
(1 row)

gaussdb=# SELECT json_depth('{"s":1, "x":2,"y":[1]}');
 json_depth 
------------
          3
(1 row)
```

json_extract(json, [VARIADIC text])

Description: The input parameters are a JSON object and several JSON paths. The JSON paths are constructed from the variable parameter list. This function extracts data of the specified JSON path from json, combines the data into a JSON array, and returns the JSON array. If json or json_path is null, null is returned. If the JSON format is incorrect, or json_path is an invalid path expression, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 7.

**Table 7** Parameters
Parameter	Type	Description	Value Range
json	json	JSON value from which paths are to be extracted.	The value must be in JSON format.
[VARIADIC text]	Variable text array	Variable parameter list, containing one or more JSON paths.	json_path must be a valid path expression.

Return type: json

Example:

-- One path is extracted.
gaussdb=# SELECT json_extract('[1, 2, {"x": 3}]', '$[2]');
 json_extract 
--------------
 {"x": 3}
(1 row)

-- Multiple paths are extracted.
gaussdb=# SELECT json_extract('["a", ["b", "c"], "d"]', '$[1]', '$[2]', '$[3]');
   json_extract    
-------------------
 [["b", "c"], "d"]
(1 row)

json_insert(json, [VARIADIC any])

Description: The input parameter is a JSON object, multiple JSON paths, and values to be inserted. JSON paths and values must be paired. This function inserts value at the position specified by json_path in json. This function can only insert data to a position where no path exists. If the specified path already exists in json, no data will be inserted. If json or json_path is null, null is returned. In addition, json_path cannot contain wildcard characters * or **. Otherwise, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 8.

**Table 8** Parameters
Parameter	Type	Description	Value Range
json	json	JSON object to which a value is to be inserted.	The value must be in JSON format.
[VARIADIC any]	Variable any array	Variable parameter list, containing one or more json_path-value pairs.	json_path must be a valid path expression, and value can be any value.

Return type: json

Example:

gaussdb=# SELECT json_insert('[1, [2, 3], {"a": [4, 5]}]', '$[3]', 2);
          json_insert          
-------------------------------
 [1, [2, 3], {"a": [4, 5]}, 2]
(1 row)

gaussdb=# SELECT json_insert('[1, [2, 3], {"a": [4, 5]}]', '$[10]', 10,'$[5]', 5);
            json_insert            
-----------------------------------
 [1, [2, 3], {"a": [4, 5]}, 10, 5]
(1 row)

json_keys(json[, json_path])

Description: The input parameters are a JSON object and an optional JSON path. If no JSON path is transferred, this function returns a JSON array of member key values of the top-layer object in the JSON object. If a JSON path is transferred, this function returns the JSON array of the top-layer member key values in the JSON object corresponding to the path. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported.

Parameters: See Table 9 Parameters.

**Table 9** Parameters
Parameter	Type	Description	Value Range
json	json	A JSON value.	The value must be in JSON format.
json_path	text	A JSON path, which is optional.	json_path must be a valid path expression.

Return type: json

Example:

gaussdb=# SELECT json_keys('{"x": 1, "y": 2, "z": 3}');
    json_keys    
-----------------
 ["x", "y", "z"]
(1 row)

gaussdb=# SELECT json_keys('[1,2,3,{"name":"Tom"}]','$[3]');
 json_keys 
-----------
 ["name"]
(1 row)

json_length(json[, json_path])

Description: The input parameters are a JSON object and an optional JSON path. If no JSON path is transferred, this function returns the length of the transferred JSON object. If a JSON path is transferred, this function returns the length of the JSON object corresponding to the path. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported.

Parameters: See Table 10.

**Table 10** Parameters
Parameter	Type	Description	Value Range
json	json	A JSON value.	The value must be in JSON format.
json_path	text	A JSON path, which is optional.	json_path must be a valid path expression.

Return type: int

Example:

gaussdb=# SELECT json_length('[1,2,3,4,5]');
 json_length 
-------------
           5
(1 row)

gaussdb=# SELECT json_length('{"name":"Tom", "age":24, "like":"football"}');
 json_length 
-------------
           3
(1 row)

json_merge([VARIADIC any])
Description: The input parameters are of the JSON type and the number of input parameters is greater than or equal to 2. The JSON objects are constructed from the variable parameter list. This function combines all input JSON parameters and returns the combination result. If the input parameter is null, null is returned. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameter: a variable parameter list. Multiple JSON objects are constructed from this list.

Return type: json
Example:
```
gaussdb=# SELECT json_merge('[1, 2]','[2]');
 json_merge 
------------
 [1, 2, 2]
(1 row)

gaussdb=# SELECT json_merge('{"b":"2"}','{"a":"1"}','[1,2]');
          json_merge          
------------------------------
 [{"a": "1", "b": "2"}, 1, 2]
(1 row)
```

json_quote(text)
Description: The input parameter is of the text type. This function uses double quotation marks ("") to enclose the input parameter into a JSON string and returns the string.

Parameter: The input parameter is the character string to be enclosed.

Return type: json
Example:
```
gaussdb=# SELECT json_quote('gauss');
 json_quote 
------------
 "gauss"
(1 row)
```

json_unquote(json)
Description: The input parameter is a JSON object. This function cancels the quotation marks of the input parameter and returns the character string.

Return type: object-json

Return type: text
Example:
```
gaussdb=# SELECT json_unquote('"gauss"');
 json_unquote 
--------------
 gauss
(1 row)
```

json_remove(json, [VARIADIC text])

Description: The input parameters are a JSON object and several JSON paths to be deleted. These JSON paths are constructed from the variable parameter list. This function deletes the value of json_path in json and returns the JSON object after deletion. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 11.

**Table 11** Parameters
Parameter	Type	Description	Value Range
json	json	A JSON value.	The value must be in JSON format.
[VARIADIC text]	Variable text array	Variable parameter list, containing one or more JSON paths.	json_path must be a valid path expression.

Return type: json

Example:

gaussdb=# SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[2]');
 json_remove 
-------------
 [1, 2]
(1 row)

gaussdb=# SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[0]','$[0]');
 json_remove 
-------------
 [[3, 4]]
(1 row)

json_replace(json, [VARIADIC any])

Description: The input parameter is a JSON object, multiple JSON paths, and values to be replaced. The JSON paths and values must be in pairs. This function replaces data in a path specified by json_path in json with a specified value and returns the modified JSON object. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 12.

**Table 12** Parameters
Parameter	Type	Description	Value Range
json	json	A JSON value.	The value must be in JSON format.
[VARIADIC any]	Variable any input parameter	Variable parameter list, containing one or more json_path-value pairs.	json_path must be a valid path expression, and value can be any value.

Return type: json

Example:

gaussdb=# SELECT json_replace('{"x": 1}', '$.x', 'true');
 json_replace  
---------------
 {"x": "true"}
(1 row)

gaussdb=# SELECT json_replace('{"x": 1}', '$.x', true, '$.x', 123, '$.x', 'asd', '$.x', null);
 json_replace 
--------------
 {"x": null}
(1 row)

json_search(json, mode_str, search_str, escape_char, json_path)

Description: Returns the path of a given string in json. It returns a path string or an array of multiple paths. If any of json, search_str, and json_path is null, null is returned. If the JSON format is incorrect or json_path is not a valid path expression, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 13 Parameters.

**Table 13** Parameters
Parameter	Type	Description	Value Range
json	json	Target JSON object.	The value must be in JSON format.
mode_str	text	The value can be one or all. If the value is one, only the first matched path is obtained. If the value is all, all matched paths are obtained.	The value must be one or all, which is case-insensitive.
search_str	text	Character string to be searched for. In normal cases, the entire character string is matched. However, the wildcard % can be used to match any number of characters, and _ can be used to match any character.	-
escape_char	text	If search_str contains a wildcard, this parameter is defined as an escape character for the wildcard. This parameter is optional. The default value is '\'. If the character is added before the wildcard, the wildcard is not considered as a wildcard.	The value can be any single character.
json_path	text	If json_path is specified, the search is performed in the path. This parameter is optional.	json_path must be a valid path expression.

Return type: text

Example:

-- In the all mode:
gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','all','abc');
  json_search   
----------------
 ["$.a", "$.b"]
(1 row)

-- In the one mode:
gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','one','abc');
 json_search 
-------------
 "$.a"
(1 row)

-- The default escape character is used.
gaussdb=# SELECT json_search('{"a":"abc","b":"a%c"}','one','a\%c');
 json_search 
-------------
 "$.b"
(1 row)

json_set(json, [VARIADIC any])

Description: The input parameter is a JSON object, multiple JSON paths, and values to be set. The JSON paths and values must be in pairs. This function uses the specified value to update the data in the path specified by json_path in json and returns the modified JSON object. If the specified path does not have any value, the value is inserted into the corresponding path. If json or json_path is null, null is returned. If the JSON format is incorrect, json_path is an invalid path expression, or json_path contains * or **, an error is reported. If the variable parameter list contains null values and has format error, the exceptions are processed based on the exception sequence. If a null value exists first, null is returned first. If a format error occurs first, an error is reported first.

Parameters: See Table 14 Parameters.

**Table 14** Parameters
Parameter	Type	Description	Value Range
json	json	A JSON value.	The value must be in JSON format.
[VARIADIC any]	Variable any array	Variable parameter list, containing one or more json_path-value pairs.	json_path must be a valid path expression, and value can be any value.

Return type: json

Example:

gaussdb=# SELECT json_set('{"s":3}','$.s','d');
  json_set  
------------
 {"s": "d"}
(1 row)

gaussdb=# SELECT json_set('{"s":3}','$.a','d','$.a','1');
      json_set      
--------------------
 {"a": "1", "s": 3}
(1 row)

json_type(json)

Description: The input parameter is a JSON object. This function returns a string, which represents the type of the given JSON value. If the input parameter is null, null is returned.

Parameter: a JSON value. For details, see Table 15.

Return type: text

**Table 15** Return value of json_type
Input Parameter Type	Return Value
JSON value of the array type	ARRAY
JSON value of the object type	OBJECT
JSON value of the character string type	STRING
JSON value of the numeric type	NUMBER
JSON value of the Boolean type	BOOLEAN
NULL	NULL

Example:

gaussdb=# SELECT json_type('{"w":{"2":3},"2":4}');
 json_type 
-----------
 OBJECT
(1 row)

gaussdb=# SELECT json_type('[1,2,2,3,3,4,4,4,4,4,4,4,4]');
 json_type 
-----------
 ARRAY
(1 row)

json_valid(json)

Description: Returns 0 or 1 to indicate whether the given parameter is a valid JSON object. If the input parameter is null, null is returned.

Return type: object-json

Return type: bigint

Example:

gaussdb=# SELECT json_valid('{"name":"Tom"}');
 json_valid 
------------
          1
(1 row)


gaussdb=# SELECT json_valid('[1,23,4,5,5]');
 json_valid 
------------
          1
(1 row)

gaussdb=# SELECT json_valid('[1,23,4,5,5]}');
 json_valid 
------------
          0
(1 row)

json_agg(any)

Description: Aggregates values into a JSON array.

Return type: array-json

Example:

gaussdb=# CREATE TABLE classes(name varchar, score int);
CREATE TABLE
gaussdb=# INSERT INTO classes VALUES('A',2);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('A',3);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('D',5);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('D',null);
INSERT 0 1
gaussdb=# SELECT * FROM classes;
 name | score 
------+-------
 A    |     2
 A    |     3
 D    |     5
 D    |      
(4 rows)

gaussdb=# SELECT name, json_agg(score) score FROM classes GROUP BY name ORDER BY name;
 name |   score   
------+-----------
 A    | [2, 3]
 D    | [5, null]
(2 rows)

gaussdb=# DROP TABLE classes;
DROP TABLE

json_object_agg(any, any)

Description: Aggregates values into a JSON object.

Return type: object-json

Example:

gaussdb=# CREATE TABLE classes(name varchar, score int);
CREATE TABLE
gaussdb=# INSERT INTO classes VALUES('A',2);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('A',3);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('D',5);
INSERT 0 1
gaussdb=# INSERT INTO classes VALUES('D',null);
INSERT 0 1
gaussdb=# SELECT * FROM classes;
 name | score 
------+-------
 A    |     2
 A    |     3
 D    |     5
 D    |      
(4 rows)

gaussdb=# SELECT json_object_agg(name, score) FROM classes GROUP BY name ORDER BY name;
     json_object_agg     
-------------------------
 { "A" : 2, "A" : 3 }
 { "D" : 5, "D" : null }
(2 rows)

gaussdb=# DROP TABLE classes;
DROP TABLE

jsonb_contained(jsonb, jsonb)
Description: Same as the operator `<@`, determines whether all elements in $1 exist at the top layer of $2.

Return type: Boolean
Example:
```
 gaussdb=# SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]');
   jsonb_contained
  -----------------
   t
  (1 row)
```

jsonb_contains(jsonb, jsonb)
Description: Same as the operator `@>`, checks whether all top-layer elements in $1 are contained in $2.

Return type: Boolean
Example:
```
 gaussdb=# SELECT jsonb_contains('[1,2,3,4]', '[1,2,3]');
   jsonb_contains
  ----------------
   t
  (1 row)
```

jsonb_exists(jsonb, text)
Description: Same as the operator `?`, determines whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.

Return type: Boolean
Example:
```
  gaussdb=# SELECT jsonb_exists('["1",2,3]', '1');
   jsonb_exists
  --------------
   t
  (1 row)
```

jsonb_exists_all(jsonb, text[])
Description: Same as the operator `?&`, checks whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.

Return type: Boolean
Example:
```
  gaussdb=# SELECT jsonb_exists_all('["1","2",3]', '{1, 2}');
   jsonb_exists_all
  ------------------
   t
  (1 row)
```

jsonb_exists_any(jsonb, text[])
Description: Same as the operator `?|`, checks whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.

Return type: Boolean
Example:
```
  gaussdb=# SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
   jsonb_exists_any
  ------------------
   t
  (1 row)
```

jsonb_cmp(jsonb, jsonb)
Description: Compares values. A positive value indicates greater than, a negative value indicates less than, and 0 indicates equal.

Return type: integer
Example:
```
gaussdb=# SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
   jsonb_cmp
  -----------
          -1
  (1 row)
```

jsonb_eq(jsonb, jsonb)
Description: Same as the operator `=`, compares two values.

Return type: Boolean
Example:
```
  gaussdb=# SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
   jsonb_eq
  ----------
   f
  (1 row)
```

jsonb_ne(jsonb, jsonb)
Description: Same as the operator `<>`, compares two values.

Return type: Boolean
Example:
```
  gaussdb=# SELECT jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
   jsonb_ne
  ----------
   t
  (1 row)
```

jsonb_gt(jsonb, jsonb)
Description: Same as the operator `>`, compares two values.

Return type: Boolean

Example:
```
 gaussdb=# SELECT jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
   jsonb_gt
  ----------
   f
  (1 row)
```

jsonb_ge(jsonb, jsonb)
Description: Same as the operator `>=`, compares two values.

Return type: Boolean
Example:
```
 gaussdb=# SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
   jsonb_ge
  ----------
   f
  (1 row)
```

jsonb_lt(jsonb, jsonb)
Description: Same as the operator `<`, compares two values.

Return type: Boolean
Example:
```
 gaussdb=# SELECT jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
   jsonb_lt
  ----------
   t
  (1 row)
```

jsonb_le(jsonb, jsonb)
Description: Same as the operator `<=`, compares two values.

Return type: Boolean
Example:
```
 gaussdb=# SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}');
   jsonb_le
  ----------
   t
  (1 row)
```

to_json(anyelement)

Description: Converts parameters to `json`.

Return type: json

Example:

 gaussdb=# SELECT to_json('{1,5}'::text[]);
    to_json
  -----------
   ["1","5"]
  (1 row)

to_jsonb(anyelement)
Description: Converts the input anyelement parameter to the JSONB type.

Return type: jsonb
- When the parameter is an empty string (''), if the SQL compatibility mode of the database is A, NULL is returned; if the SQL compatibility mode of the database is PG, an empty string is returned. This is because empty strings are treated as NULL in A compatibility mode.
- When the parameter is of the DATE type, if the database SQL compatibility mode is A, the returned result contains hour, minute, and second; if the database SQL compatibility mode is PG, the returned result does not contain hour, minute, and second. This is because DATE will be replaced by TIMESTAMP(0) WITHOUT TIME ZONE in A compatibility mode.
Example:
```
gaussdb=# SELECT to_jsonb(ARRAY[1, 2, 3, 4]);
   to_jsonb
--------------
 [1, 2, 3, 4]
(1 row)
```
jsonb_hash(jsonb)
Description: Performs the hash operation on JSONB.

Return type: integer

Example:
```
 gaussdb=# SELECT jsonb_hash('[1,2,3]');
 jsonb_hash
------------
 -559968547
(1 row)
```

Other Functions

 json_agg_transfn
 json_agg_finalfn
 json_object_agg_transfn
 json_object_agg_finalfn

Parent topic: Functions and Operators

Previous topic: Text Search Functions and Operators

Next topic: HLL Functions and Operators

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.See the reply and handling status in My Cloud VOC.

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

For any further questions, feel free to contact us through the chatbot.

Chatbot