JSON/JSONB支持的函数
array_to_json(anyarray [, pretty_bool])
描述：返回JSON类型的数组。将一个多维数组组成一个JSON数组。如果pretty_bool为true，将在一维元素之间添加换行符。
返回类型：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])
描述：返回JSON类型的行。如果pretty_bool为true，将在第一级元素之间添加换行符。
返回类型：json
示例：
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)
描述：同操作符`->`, 返回数组中指定下标的元素。
返回类型：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)
描述：同操作符`->>`, 返回数组中指定下标的元素。
返回类型：text、text
示例：
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)
描述：同操作符`->`, 返回对象中指定键对应的值。
返回类型：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)
描述：同操作符`->>`, 返回对象中指定键对应的值。
返回类型：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[])
描述：等价于操作符`#>`。根据第二个参数所指的路径，查找json，并返回。
返回类型：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[])
描述：同操作符`#>`。根据第二个参数所指的路径，查找json，并返回。
返回类型：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[])
描述：等价于操作符`#>>`。根据第二个参数所指的路径，查找json，并返回。
返回类型：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[])
描述：同操作符`#>>`。根据第二个参数所指的路径，查找json，并返回。
返回类型：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)
描述：拆分数组，每一个元素返回一行。
返回类型：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)
描述：拆分数组，每一个元素返回一行。
返回类型：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)
描述：返回数组长度。
返回类型：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)
描述：将对象的每个键值对拆分转换成一行两列。
返回类型：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)
描述：将对象的每个键值对拆分转换成一行两列。
返回类型：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)
描述：返回对象中顶层的所有键。
返回类型：SETOF text
gaussdb=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
json_object_keys
------------------
f1
f2
f1
(3 rows)
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])
描述：第一个参数必须是一个复合类型的参数。将会把object-json里的每个对键值进行拆分，以键当做列名，与第一个参数中的列名进行匹配查找，并填充到第一个参数的格式中。
返回类型：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])
描述：参考上述函数json_populate_record、jsonb_populate_record，对第二个参数数组的每一个元素进行上述参数函数的操作，因此这也要求第二个参数数组的每个元素都是object-json类型的。
返回类型：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)
描述：检测json类型。
返回类型：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"] )
描述：从一个可变参数列表构造出一个JSON数组。
返回类型：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"] )
描述：从一个可变参数列表构造出一个JSON对象，其入参必须为偶数个，两两一组组成键值对。注意键不可为null。
返回类型：object-json
gaussdb=# SELECT json_build_object(1,2);
json_build_object
-------------------
{"1" : 2}
(1 row)
jsonb_build_object( [VARIADIC "any"] )
描述：从一个可变参数列表构造出一个JSONB对象，其入参必须为偶数个，两两一组组成键值对。注意键值不可为NULL。
返回类型：object-jsonb
- 当可变参数列表的元素存在空字符串（''）时，若数据库SQL兼容模式为A，则对应的元素位置返回结果为NULL；若数据库SQL兼容模式为PG, 则对应的元素位置返回结果为空字符串。这是因为A兼容模式时空字符串作为NULL处理。
- 当可变参数列表的元素存在DATE类型时，若数据库SQL兼容模式为A，则对应的元素位置返回结果包含时分秒；若数据库SQL兼容模式为PG, 则对应的元素位置返回结果不包含时分秒。这是因为A兼容模式DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。
示例：
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)
描述：正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组，把键当做列名，去匹配填充AS显示指定的记录的结构。重载1的boolean入参表示是否支持对象嵌套，true为支持，false为不支持，只有一个入参的时候，默认支持嵌套，且入参仅有一个时可将嵌套json对象转换为行类型及支持方括号格式数组的解析。对于时间类型date，ORA兼容模式下相当于timestamp(0)，因此与PG兼容模式相比，在显示上会多出00:00:00时分秒字符串。
返回类型：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)
描述：参考函数json_to_record，对数组内每个元素，执行上述函数的操作，因此这要求数组内的每个元素都得是object-json，boolean也可参考函数json_to_record，true为支持解析嵌套对象，false为不支持，即是否支持json对象中元素的value值也是一个json对象。
返回类型：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[])
描述：从一个文本数组构造一个object-json。这是个重载函数，当入参为一个文本数组的时候，其数组长度必须为偶数，数组内的成员被当作交替出现的键值对。两个文本数组的时候，第一个数组认为是键，第二个认为是值，两个数组长度必须相等。键值不可为NULL。
返回类型：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"] )
描述：从一个可变参数列表构造出一个json对象，其入参必须为偶数个，两两一组组成键值对。键值为null报错；参数格式为奇数报错。
参数：可变参数列表，入参是一个或多个成对的key-value组合。
返回类型：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)
此函数在MYSQL模式数据库中，且GUC参数 b_format_version = '5.7'和b_format_dev_version = 's1'时生效，生效时会替换原有json_object行为。
json_append/json_array_append(json, [VARIADIC "any"])
描述：接受一个json入参和一个可变参数列表。从一个可变参数列表构造出若干对json_path和value对，向json类型中指定的路径追加一个值，并返回修改后的json。json_append与json_array_append完全一致。如果有任何参数为null返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：第一个入参必须为符合格式的json，第二个入参为可变的参数列表，json_path-value对将从可变参数列表中构造。详情请参见表1 参数说明。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
需要被修改的json值。 |
符合json格式。 |
|
[VARIADIC "any"] |
可变any数组 |
可变参数列表，包含可变json_path-value数量。 |
json_path必须是一个有效的路径表达式，value可以是任意值。 |
返回类型：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"])
描述：从一个可变参数列表构造出一个数组，并返回数组形式的json。如果该函数没有任何参数，则返回一个空的数组形式的json。
参数：入参为可变参数列表，这些列表中的值可以是任意类型。
返回类型：json
--不输入入参，返回一个空的数组形式的json
gaussdb=# SELECT json_array();
json_array
------------
[]
(1 row)
--入参可以是任意类型
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"])
描述：从一个可变参数列表中，构造一个或者多个json_path和value的路径-值对，向json中的json_path指定数组路径插入一个值value并返回新的json。如果指定的路径上有值，则在该路径上插入，并将已存在的值后移。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表2 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
需要修改的json值。 |
必须满足json的格式。 |
|
[VARIADIC "any"] |
可变any数组 |
可变参数列表，可以支持一个或多个json_path-value对。 |
json_path必须是一个有效的路径表达式，value可以是任意值。 |
返回类型：json
--一对json_path-value用例
gaussdb=# SELECT json_array_insert('[1, [2, 3]]', '$[1]', 4);
json_array_insert
-------------------
[1, 4, [2, 3]]
(1 row)
--多个json_path-value用例
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])
描述：入参为两个json对象和一个可选的json_path路径。如果不选定json路径，则判断target_json是否包含了candidate_json。如果选定路径，则在target_json指定路径json_path指向的json中，判断是否包含candidate_json。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表3 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
target_json |
json |
目标json对象，需要判断candidate_json是否被该json包含。 |
必须满足json的格式。 |
|
candidate_json |
json |
json子对象，需要判断是否被target_json包含。 |
必须满足json的格式。 |
|
json_path |
text |
可选参数，指定json的路径json_path。如果指定，则使用target_json的json_path指定路径判断包含关系。 |
json_path必须是一个有效的路径表达式。 |
返回类型：bigint类型，如果包含返回1，如果不包含返回0。
--不指定路径
gaussdb=# SELECT json_contains('[1, 2, {"x": 3}]', '{"x":3}');
json_contains
---------------
1
(1 row)
--指定路径
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])
描述：该函数用于检查一个json对象在指定的路径上是否有值，这个路径可以是多个。第一个入参为json对象，第二个入参为字符串“one”或“all”，它指示是否检查所有的路径。第三个入参为一个可变参数列表，所有的json_path路径将会从这个可变参数列表中构造。如果路径根据模式判断存在返回1，如果不存在返回0。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表4 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
需要传入的json对象。 |
必须满足json的格式。 |
|
mode_str |
text |
字符串“one”或者 “all”，如果是“one”，其中一个路径存在返回1，否则返回0；如果是“all”，所有路径存在返回1，否则返回0。 |
必须为字符串“one”或“all”，不区分大小写。 |
|
[VARIADIC text] |
可变text数组 |
可变参数列表，从此列表中构造所有的json_path路径。 |
json_path必须为有效的路径表达式。 |
返回类型：bigint类型
--all场景，需要所有路径都存在
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)
--one场景，其中一个路径存在即可
gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'one', '$[0]', '$[1]', '$[5]');
json_contains_path
--------------------
1
(1 row)
json_depth(json)
描述：入参为一个json对象，此函数用于返回传入json对象的最大深度。入参为null，返回null。
参数：入参为一个需要查询深度的json。入参如果不是有效的json则报错。
返回类型：int
--空数组的深度为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])
描述：入参为一个json对象，和若干个json_path路径，json路径从可变参数列表中构造，此函数在json中提取指定json_path路径的数据，并将其组合成一个json数组返回。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表5 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
需要提取路径的json值。 |
必须满足json的格式。 |
|
[VARIADIC text] |
可变text数组 |
可变参数列表，可以支持一个或多个json_path路径。 |
json_path必须是一个有效的路径表达式。 |
返回类型：json
--提取一个路径的值
gaussdb=# SELECT json_extract('[1, 2, {"x": 3}]', '$[2]');
json_extract
--------------
{"x": 3}
(1 row)
--提取多个路径的值
gaussdb=# SELECT json_extract('["a", ["b", "c"], "d"]', '$[1]', '$[2]', '$[3]');
json_extract
-------------------
[["b", "c"], "d"]
(1 row)
json_insert(json, [VARIADIC any])
描述：入参为一个json对象，若干个json_path路径和需要插入的值，json_path和值必须两两配对，此函数在json中的json_path路径指定的位置插入一个value值，此函数只能将数据插入到不存在路径。如果json中已经存在指定的路径，则不会插入数据。json和json_path中存在null值返回null。并且json_path中不能存在“*”、“**”通配符，否则报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表6 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
需要被插入值的json值。 |
必须满足json的格式。 |
|
[VARIADIC any] |
可变any数组 |
可变参数列表，可以支持一个或多个json_path-value对。 |
json_path必须是一个有效的路径表达式，value可以是任意值。 |
返回类型：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])
描述：入参为一个json对象，和一个可选的json_path路径。如果没有传入json_path路径，此函数返回json对象中最上层对象的成员key值的json数组。如果传入了json_path路径，此函数返回该路径对应的json对象中最上层的成员key值的json数组。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。
参数：如表7 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
一个json值。 |
必须满足json的格式。 |
|
json_path |
text |
可选的，一个json_path路径。 |
json_path必须是一个有效的路径表达式。 |
返回类型：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])
描述：入参为一个json对象，和一个可选的json_path路径。如果没有传入json_path路径，此函数返回传入json对象的长度。如果传入了json_path路径，此函数返回该路径对应的json对象的长度。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。
参数：如表8 参数说明所示。
|
参数名称 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
一个json值。 |
必须满足json的格式。 |
|
json_path |
text |
可选的，一个json_path路径 |
json_path必须是一个有效的路径表达式。 |
返回类型：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])
描述：入参为多个json类型，入参的个数大于等于2，json对象从可变参数列表中构造；此函数将所有传入的json进行合并，并返回合并后的结果。入参中有null，返回null。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：可变参数列表，多个json对象会从这个列表中构造。
返回类型：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)
描述：入参为一个text类型，此函数使用双引号将入参包装成一个字符串形式的json类型并返回。
参数：入参为需要被包装的字符串。
返回类型：json
gaussdb=# SELECT json_quote('gauss');
json_quote
------------
"gauss"
(1 row)
json_unquote(json)
描述：入参为一个json，此函数将取消入参的双引号引用，并返回取消后的字符串。
参数：一个json值。
返回类型：text
gaussdb=# SELECT json_unquote('"gauss"');
json_unquote
--------------
gauss
(1 row)
json_remove(json, [VARIADIC text])
描述：入参为一个json对象，和若干个需要被删除的json_path路径，这些json_path路径从可变参数列表中构造。此函数将删除json中指定json_path路径的值，并返回删除后的json。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表9 参数说明所示。
|
参数说明 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
一个json值。 |
必须满足json的格式。 |
|
[VARIADIC text] |
可变text数组 |
可变参数列表，可以支持一个或多个json_path路径。 |
json_path必须是一个有效的路径表达式。 |
返回类型：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])
描述：入参为一个json对象，和若干个json_path路径和需要替换的值，json_path和值必须两两配对；此函数在一个json中使用指定的值来替换指定json_path路径上的数据，并返回修改后的json。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表10 参数说明所示。
|
参数说明 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
一个json值。 |
必须满足json的格式。 |
|
[VARIADIC any] |
可变any入参 |
可变参数列表，可以支持一个或多个json_path-value对。 |
json_path必须是一个有效的路径表达式，value可以是任意值。 |
返回类型：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)
描述：函数返回一个给定字符串在一个 json中的路径。它返回一个路径字符串或者由多个路径组成的数组。如果参数json、search_str或json_path任意一个是null，返回null。如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表11 参数说明所示。
|
参数说明 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
目标的json。 |
必须满足json的格式。 |
|
mode_str |
text |
字符串“one”或者 “all”，如果是“one”，表示只获取第一个匹配到的路径；如果是“all”，则会匹配所有符合的路径。 |
必须是字符串“one”或者“all”，不区分大小写。 |
|
search_str |
text |
被搜索的字符串，正常情况下为全字匹配，但是可以使用通配符：%: 匹配任意数量的任意字符，_: 匹配一个任意字符。 |
- |
|
escape_char |
text |
可选的，如果search_str中包含通配符，则该参数定义为通配符的转义字符。默认为'\'。若在通配符前加上该字符，则不将该字符看做通配符。 |
可以是任意单个字符。 |
|
json_path |
text |
可选的，若指定json_path，则在该路径下搜索。 |
json_path必须是一个有效的路径表达式。 |
返回类型：text
--all模式
gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','all','abc');
json_search
----------------
["$.a", "$.b"]
(1 row)
--one模式
gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','one','abc');
json_search
-------------
"$.a"
(1 row)
--使用默认转义字符
gaussdb=# SELECT json_search('{"a":"abc","b":"a%c"}','one','a\%c');
json_search
-------------
"$.b"
(1 row)
json_set(json, [VARIADIC any])
描述：入参为一个json对象，若干个json_path路径和需要设置的值，json_path和值必须两两配对；此函数在一个json中使用指定的值来更新指定json_path路径上的数据，并返回修改后的json；如果指定的路径不存在值，则在对应的路径上插入值。json和json_path路径中有null值返回null，如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中，则按异常的先后顺序处理，先有null值返回null，先有报错则报错。
参数：如表12 参数说明所示。
|
参数说明 |
类型 |
描述 |
取值范围 |
|---|---|---|---|
|
json |
json |
一个json值。 |
必须满足json的格式。 |
|
[VARIADIC any] |
可变any数组 |
可变参数列表，可以支持一个或多个json_path-value对。 |
json_path必须是一个有效的路径表达式，value可以是任意值。 |
返回类型：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)
描述：入参为一个json对象，函数返回一个字符串，它代表了给定的 json值的类型。入参为null返回null。
参数：一个json值。详情请参见表13 json_type返回值。
返回类型：text
|
入参类型 |
返回值 |
|---|---|
|
数组类型的json值 |
ARRAY |
|
对象类型的json值 |
OBJECT |
|
字符串类型的json值 |
STRING |
|
数值类型的json值 |
NUMBER |
|
boolean类型的json值 |
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)
描述：函数返回 0 和 1 来指示给定的参数是否是一个有效的json。入参为null返回null。
参数：一个json值。
返回类型：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)
描述：将值聚集为json数组。
返回类型：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)
描述：将值聚集为json对象。
返回类型：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)
描述：同操作符 `<@`, 判断参数一中的所有元素是否在第二个参数的顶层存在。
返回类型：boolean
gaussdb=# SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]');
jsonb_contained
-----------------
t
(1 row)
jsonb_contains(jsonb, jsonb)
描述：同操作符 `@>`, 判断参数一中的顶层所有元素是否包含在第二个参数的所有元素。
返回类型：boolean
gaussdb=# SELECT jsonb_contains('[1,2,3,4]', '[1,2,3]');
jsonb_contains
----------------
t
(1 row)
jsonb_exists(jsonb, text)
描述：同操作符 `?`, 字符串参数二是否存在第一个参数的顶层以key\elem\scalar的形式存在。
返回类型：boolean
gaussdb=# SELECT jsonb_exists('["1",2,3]', '1');
jsonb_exists
--------------
t
(1 row)
jsonb_exists_all(jsonb, text[])
描述：同操作符 `?&`, 字符串数组参数二里面，是否所有的元素，都在第一个参数的顶层以key\elem\scalar的形式存在。
返回类型：boolean
gaussdb=# SELECT jsonb_exists_all('["1","2",3]', '{1, 2}');
jsonb_exists_all
------------------
t
(1 row)
jsonb_exists_any(jsonb, text[])
描述：同操作符 `?|`, 字符串数组参数二里面，是否存在的元素，在第一个参数的顶层以key\elem\scalar的形式存在。
返回类型：boolean
gaussdb=# SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
jsonb_exists_any
------------------
t
(1 row)
jsonb_cmp(jsonb, jsonb)
描述：比较大小，正数代表大于，负数代表小于，0表示相等。
返回类型：integer
gaussdb=# SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
jsonb_cmp
-----------
-1
(1 row)
jsonb_eq(jsonb, jsonb)
描述：同操作符 `=`, 比较两个值的大小。
返回类型：boolean
gaussdb=# SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
jsonb_eq
----------
f
(1 row)
jsonb_ne(jsonb, jsonb)
描述：同操作符 `<>`, 比较两个值的大小。
返回类型：boolean
gaussdb=# SELECT jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
jsonb_ne
----------
t
(1 row)
jsonb_gt(jsonb, jsonb)
描述：同操作符 `>`, 比较两个值的大小。
返回类型：boolean
示例：
gaussdb=# SELECT jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
jsonb_gt
----------
f
(1 row)
jsonb_ge(jsonb, jsonb)
描述：同操作符 `>=`, 比较两个值的大小。
返回类型：boolean
gaussdb=# SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
jsonb_ge
----------
f
(1 row)
jsonb_lt(jsonb, jsonb)
描述：同操作符 `<`, 比较两个值的大小。
返回类型：boolean
gaussdb=# SELECT jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
jsonb_lt
----------
t
(1 row)
jsonb_le(jsonb, jsonb)
描述：同操作符 `<=`, 比较两个值的大小。
返回类型：boolean
gaussdb=# SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}');
jsonb_le
----------
t
(1 row)
to_json(anyelement)
描述：把参数转换为`json`
返回类型：json
gaussdb=# SELECT to_json('{1,5}'::text[]);
to_json
-----------
["1","5"]
(1 row)
to_jsonb(anyelement)
描述：把输入的anyelement参数转换为`jsonb`类型。
返回类型：jsonb
- 当参数为空字符串（''）时，若数据库SQL兼容模式为A，则返回结果为NULL；若数据库SQL兼容模式为PG, 则返回结果为空字符串。这是因为A兼容模式时空字符串作为NULL处理。
- 当参数为DATE类型时，若数据库SQL兼容模式为A，则返回结果包含时分秒；若数据库SQL兼容模式为PG, 则返回结果不包含时分秒。这是因为A兼容模式DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。
gaussdb=# SELECT to_jsonb(ARRAY[1, 2, 3, 4]);
to_jsonb
--------------
[1, 2, 3, 4]
(1 row)
jsonb_hash(jsonb)
描述：对jsonb进行hash运算。
返回类型：integer
示例：
gaussdb=# SELECT jsonb_hash('[1,2,3]');
jsonb_hash
------------
-559968547
(1 row)
其他函数
json_agg_transfn json_agg_finalfn json_object_agg_transfn json_object_agg_finalfn
