Functions Supported by JSON/JSONB
array_to_json(anyarray [, pretty_bool])
Description: Returns a JSON array. It converts a multidimensional array into a JSON array. Line feeds will be added between one-dimensional elements if pretty_bool is true.
Return type: json
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 a JSON row. 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: Returns an array element with the specified index (same as the `->` operator).
Return type: json, jsonb
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: Returns an array element with the specified index (same as the `->>` operator).
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: Returns the value of a specified key in an object (same as the `->` operator).
Return type: json, json
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: Returns the value of a specified key in an object (same as the `->>` operator).
Return type: text, text
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: Searches for and returns json based on the path specified in the second parameter (same as the `#>` operator).
Return type: json, jsonb
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: Searches for and returns json based on the path specified in the second parameter (same as the `#>` operator).
Return type: json, jsonb
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: Searches for and returns json based on the path specified in the second parameter (same as the `#>>` operator).
Return type: text, text
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: Searches for and returns json based on the path specified in the second parameter (same as the `#>>` operator).
Return type: text, text
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
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
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
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)
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)
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
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: The first parameter must be a composite 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 the first parameter and fill in the format of the first parameter.
Return type: anyelement, anyelement
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 array of the second parameter by referring to the json_populate_record and jsonb_populate_record functions. Therefore, each element in the array of the second parameter must be of the object-json type.
Return type: setof anyelement, setof anyelement
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
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
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 input must contain an even number of parameters grouped into key-value pairs. Note that the value of a key cannot be null.
Return type: object-json
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 input must contain an even number of parameters grouped into key-value pairs. Note that the value of a key cannot be null.
Return type: object-jsonb

- In A-compatible mode, if any element in the variable-parameter list is an empty string (''), the function will return NULL at the location of that element. However, in PG-compatible mode, the function will return an empty string instead. This is because empty strings are treated as NULL in A-compatible mode.
- In A-compatible mode, if any element in the variable-parameter list is of the DATE type, the function will return the hour, minute, and second at the location of that element. However, in PG-compatible mode, the function will not return the hour, minute, or second. This is because DATE will be replaced by TIMESTAMP(0) WITHOUT TIME ZONE in A-compatible 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 input parameter bool of overload 1 specifies whether object nesting is allowed (true: allowed; false: disallowed). By default, object nesting is allowed when there is only one input parameter. In this case, the nested JSON object can be converted into the row type, and arrays enclosed in square brackets ([]) can be parsed. Dates of the time type are equivalent to timestamp(0) in A-compatible mode. Compared with that in the PG-compatible mode, the value also displays the 00:00:00 (hour, minute, and second) character string.
Return type: record
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: SETOF record
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
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 input must contain an even number of parameters grouped into key-value pairs. If the key-value pair is null, an error is reported. If the parameter is in the odd format, an error is reported.
Parameter: a variable-parameter list containing one or more key-value pairs.
Return type: json
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 in B-compatible databases where the GUC parameter b_format_version is '5.7' and b_format_dev_version is 's1'. Once in effect, it will replace the original json_object behavior.
json_append/json_array_append(json, [VARIADIC "any"])
Description: Constructs several json_path-value pairs from a variable-parameter list, appends a value to the path specified in json, and returns the modified json. json_append is the same as json_array_append. If any input parameter is null, the function will return null. An error will be reported if json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: The first input parameter must be JSON in the correct format, and the second input parameter must be a variable-parameter list. json_path-value pairs will be constructed from the variable-parameter list. For details, see Table 1.
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 the number of variable json_path-value pairs. |
json_path must be a valid path expression, and value can be any value. |
Return type: json
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: a variable-parameter list, wherein values can be of any type.
Return type: json
-- 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 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, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 2.
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 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 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 JSON path. If no JSON path is specified, the function will check whether target_json contains candidate_json. If a path is specified, the function will check whether candidate_json is contained in the json_path specified in target_json. If json or json_path is null, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 3.
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.
-- 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 this list. If the path is detected based on the mode, the function will return 1. If no path is detected, the function will return 0 instead. If json or json_path is null, the function will return null. If the json format is incorrect or json_path is an invalid path expression, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 4.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
json |
json |
JSON object to be passed. |
The value must be in JSON format. |
mode_str |
text |
one means that the function will return 1 upon detecting a single path and 0 if no path is detected. all means that the function will return 1 only when it detects all paths; otherwise, it will return 0. |
The value must be one or all, which is case-insensitive. |
[VARIADIC text] |
Variable text array |
A variable-parameter list from which all JSON paths will be constructed. |
json_path must be a valid path expression. |
Return type: bigint
-- 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, the function will return null.
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
-- 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: Extracts the data of JSON paths from a JSON object, merges this data into a JSON array and returns the array. These JSON paths are constructed from the variable-parameter list. Both the JSON paths and JSON object are specified in the input parameters. If json or json_path is null, the function will return null. If the json format is incorrect or json_path is an invalid path expression, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 5.
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 |
A variable-parameter list containing one or more JSON paths. |
json_path must be a valid path expression. |
Return type: json
-- 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 both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 6.
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 |
A 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
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 specified, this function returns a JSON array of member key values of the top-layer object in the JSON object. If a JSON path is specified, 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, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error.
Parameters: See Table 7.
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
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 specified, this function returns the length of the specified JSON object. If a JSON path is specified, this function returns the length of the JSON object corresponding to the path. If json or json_path is null, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error.
Parameters: See Table 8.
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
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: Merges the JSON objects specified in the input parameters (more than two) and returns the result. These JSON objects are constructed from the variable-parameter list. If any input parameter is null, the function will return null. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameter: a variable-parameter list from which multiple JSON objects are constructed.
Return type: json
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
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 double quotation marks of the input parameter and returns the character string.
Parameter: a JSON value.
Return type: text
gaussdb=# SELECT json_unquote('"gauss"'); json_unquote -------------- gauss (1 row)
json_remove(json, [VARIADIC text])
Description: Deletes JSON paths from a JSON object and returns the resulting JSON object. These JSON paths are constructed from the variable-parameter list. Both the JSON paths and JSON object are specified in the input parameters. If json or json_path is null, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 9.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
json |
json |
A JSON value. |
The value must be in JSON format. |
[VARIADIC text] |
Variable text array |
A variable-parameter list containing one or more JSON paths. |
json_path must be a valid path expression. |
Return type: json
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, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 10.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
json |
json |
A JSON value. |
The value must be in JSON format. |
[VARIADIC any] |
Variable any input parameter |
A 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
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: Obtains the path of a given character string in json. The function returns a path string or an array of multiple paths. If json, search_str, or json_path is null, the function will return null. If the json format is incorrect or json_path is an invalid path expression, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 11.
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
-- 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, the function will return null. If json is in an incorrect format, json_path is an invalid path expression, or json_path includes * or **, the function will report an error. If the variable-parameter list contains both null values and format errors, the exceptions are processed in the order of their occurrence. For example, if a null value is encountered first, the function will return null. If a format error is detected first, the function will report an error.
Parameters: See Table 12.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
json |
json |
A JSON value. |
The value must be in JSON format. |
[VARIADIC any] |
Variable any array |
A 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
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, the function will return null.
Parameter: a JSON value. For details, see Table 13.
Return type: text
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 |
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, the function will return null.
Parameter: a JSON value.
Return type: bigint
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
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
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: Checks whether all elements in the first parameter are present at the top layer of the second parameter (same as the `<@` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]');
jsonb_contained
-----------------
t
(1 row)
jsonb_contains(jsonb, jsonb)
Description: Checks whether all top-layer elements in the first parameter are contained in the second parameter (same as the `@>` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_contains('[1,2,3,4]', '[1,2,3]');
jsonb_contains
----------------
t
(1 row)
jsonb_exists(jsonb, text)
Description: Checks whether the second string parameter is present at the top layer of the first parameter in key\elem\scalar format (same as the `?` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_exists('["1",2,3]', '1');
jsonb_exists
--------------
t
(1 row)
jsonb_exists_all(jsonb, text[])
Description: Checks whether all elements of the second string array parameter are present at the top layer of the first parameter in key\elem\scalar format (same as the `?&` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_exists_all('["1","2",3]', '{1, 2}');
jsonb_exists_all
------------------
t
(1 row)
jsonb_exists_any(jsonb, text[])
Description: Checks whether any element of the second string array parameter is present at the top layer of the first parameter in key\elem\scalar format (same as the `?|` operator).
Return type: Boolean
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
gaussdb=# SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
jsonb_cmp
-----------
-1
(1 row)
jsonb_eq(jsonb, jsonb)
Description: Compares two values (same as the `=` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
jsonb_eq
----------
f
(1 row)
jsonb_ne(jsonb, jsonb)
Description: Compares two values (same as the `<>` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
jsonb_ne
----------
t
(1 row)
jsonb_gt(jsonb, jsonb)
Description: Compares two values (same as the `>` operator).
Return type: Boolean
Example:
gaussdb=# SELECT jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
jsonb_gt
----------
f
(1 row)
jsonb_ge(jsonb, jsonb)
Description: Compares two values (same as the `>=` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
jsonb_ge
----------
f
(1 row)
jsonb_lt(jsonb, jsonb)
Description: Compares two values (same as the `<` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
jsonb_lt
----------
t
(1 row)
jsonb_le(jsonb, jsonb)
Description: Compares two values (same as the `<=` operator).
Return type: Boolean
gaussdb=# SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}');
jsonb_le
----------
t
(1 row)
to_json(anyelement)
Description: Converts parameters into `json`.
Return type: json
gaussdb=# SELECT to_json('{1,5}'::text[]);
to_json
-----------
["1","5"]
(1 row)
to_jsonb(anyelement)
Description: Converts the input anyelement parameter into the `jsonb` type.
Return type: jsonb

- In A-compatible mode, if the parameter is an empty string (''), the function will return NULL. However, in PG-compatible mode, the function will return an empty string instead. This is because empty strings are treated as NULL in A-compatible mode.
- In A-compatible mode, if the parameter type is DATE, the function will return the hour, minute, and second. However, in PG-compatible mode, the function will not return the hour, minute, or second. This is because DATE will be replaced by TIMESTAMP(0) WITHOUT TIME ZONE in A-compatible mode.
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
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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot