KingbaseES Json 系列十一--Json数组操作函数(JSONB_ARRAY_ELEMENTS,JSONB_ARRAY_ELEMENTS_TEXT,JSONB_ARRAY_LENGTH,JSON_ARRAY_ELEMENTS,JSON_ARRAY_ELEMENTS_TEXT,JSON_ARRAY_LENGTH,JSON_ARRAYAGG)
JSON 数据类型是用来存储 JSON（JavaScript Object Notation）数据的。KingbaseES为存储JSON数据提供了两种类型:JSON和 JSONB。JSON 和 JSONB 几乎接受完全相同的值集合作为输入。
本文将主要介绍Kingbase数据库的Json数组操作函数部分。
准备数据：
CREATE TABLE "public"."jsontable" (
"id" integer NULL,
"jsondata" json NULL,
"jsonvarchar" varchar NULL,
"jsonarray" json NULL,
"jsonrecord" json NULL,
"jsonset" json NULL
);
INSERT INTO "public"."jsontable" ("id","jsondata","jsonvarchar","jsonarray","jsonrecord","jsonset") VALUES
(1,'{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}}','[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]','{"a":1,"b":"bcol","c":"cc"}','[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]'),
(2,'{"a":[1,2,3,4,5]}','{"a": [1, 2, 3, 4, 5]}','[1,2,3,4,5]','{"a":1,"b":"bcol","c":""}','[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}]'),
(3,'{"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}}','{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}','[{"f1":1,"f2":null},2,null,3]','{"a":1,"b":"bcol","d":"dd"}','[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}]');
CREATE TABLE "public"."comtable" (
"id" integer NULL,
"name" character varying(10 char) NULL
);
INSERT INTO "public"."comtable" ("id","name") VALUES
(1,'a'),
(2,'b'),
(3,'c');
json函数列表
json函数简介
JSONB_ARRAY_ELEMENTS
功能：
JSON处理函数，将传入值包括空值聚集成一个JSON数组。
用法：
jsonb_array_elements(jsonb)
示例：
demo=# select jsonb_array_elements('{"a": [1, 2, 3, 4, 5]}') ;
错误: 无法从一个对象里提取元素
demo=# select jsonb_array_elements(' [{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
jsonb_array_elements
----------------------------------
{"a": 1, "b": "bcol", "c": "cc"}
{"a": 1, "b": "bcol", "d": ""}
(2 行记录)
JSONB_ARRAY_ELEMENTS_TEXT
功能：
JSON处理函数，把一个JSON数组扩展成 text 值集合。
用法：
jsonb_array_elements_text(jsonb)
示例：
demo=# select jsonb_array_elements_text('{"a": [1, 2, 3, 4, 5]}') ;
错误: 无法从一个对象里提取元素
demo=# select jsonb_array_elements_text(' [{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
jsonb_array_elements_text
----------------------------------
{"a": 1, "b": "bcol", "c": "cc"}
{"a": 1, "b": "bcol", "d": ""}
(2 行记录)
demo=# select jsonb_array_elements_text(jsonarray) from jsontable ;
jsonb_array_elements_text
----------------------------
1
true
[1, [2, 3]]
{"f1": 1, "f2": [7, 8, 9]}
false
stringy
1
2
3
4
5
{"f1": 1, "f2": null}
2
3
(16 行记录)
JSONB_ARRAY_LENGTH
功能：
JSON处理函数，返回最外层JSON数组中的元素数量。
用法：
jsonb_array_length(jsonb)
示例：
demo=# select jsonb_array_length('{"a": [1, 2, 3, 4, 5]}');
错误: 无法从一个非数组里得到数组的长度
demo=# select jsonb_array_length(' [{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
jsonb_array_length
--------------------
2
(1 行记录)
JSON_ARRAY_ELEMENTS
功能：
JSON处理函数，将传入值包括空值聚集成一个JSON数组。
用法：
json_array_elements(json)
示例：
参照JSONB_ARRAY_ELEMENTS使用示例
JSON_ARRAY_ELEMENTS_TEXT
功能：
JSON处理函数，把一个JSON数组扩展成 text 值集合。
用法：
json_array_elements_text(json)
示例：
参照JSONB_ARRAY_ELEMENTS_TEXT使用示例
JSON_ARRAY_LENGTH
功能：
JSON处理函数，返回最外层JSON数组中的元素数量。
用法：
json_array_length(json)
示例：
参照JSONB_ARRAY_LENGTH使用示例
JSON_ARRAYAGG
功能：
JSON处理函数，函数将提供的JSON数据聚合到JSON数组中。
用法：
JSON_ARRAYAGG (
[ value_expression ]
[ ORDER BY sort_expression ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
)
value_expression ：value_expression是一个表达式，它为JSON值提供其类型的输入。。
ORDER BY sort_expression ：输入数据的排序规则。
{ NULL | ABSENT } ON NULL ：指定函数在value_expression计算结果为null时的行为。
RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ]]：指定生成数据的类型。支持以下类型：json、jsonb、bytea和字符串类型（text、char、varchar和nchar），还支持VARCHAR2、BLOB、CLOB类型。
示例：
demo=# select json_arrayagg(name) from comtable ;
json_arrayagg
-----------------
["a", "b", "c"]
(1 行记录)
demo=# select json_arrayagg(jsonarray) from jsontable ;
json_arrayagg
-------------------------------------------------------------------------------------------------------------
[[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"], [1,2,3,4,5], [{"f1":1,"f2":null},2,null,3]]
(1 行记录)
-- ORDER BY数据排序规则
demo=# select json_arrayagg(name order by id desc) from comtable ;
json_arrayagg
-----------------
["c", "b", "a"]
(1 行记录)
-- 指定json值为null时的处理
demo=# select json_arrayagg('' null on null);
json_arrayagg
---------------
[null]
(1 行记录)
demo=# select json_arrayagg('' absent on null);
json_arrayagg
---------------
[]
(1 行记录)