KingbaseES Json 系列六--Json记录操作函数一(JSONB_TO_RECORD,JSONB_TO_RECORDSET,JSON_TO_RECORD,JSON_TO_RECORDSET)
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"}]');
json函数列表
json函数简介
JSONB_TO_RECORD
功能：
JSON函数，从一个JSON对象构建一个任意的记录，正如所有返回 record 的函数一样，调用者必须用一个 AS 子句显式的定义记录的结构。
用法：
jsonb_to_record(jsonb)
示例：
-- 解析json数据
demo=# SELECT * FROM jsonb_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int,b text);
a | b
---+-----------
1 | [1, 2, 3]
(1 行记录)
-- 数据value的类型需要能够隐式转换到record的匹配类型中
demo=# SELECT * FROM jsonb_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int,b text, c boolean[]);
错误: 无效的类型 boolean 输入语法: "2"
demo=# SELECT * FROM jsonb_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int,b text, c int[]);
a | b | c
---+-----------+---------
1 | [1, 2, 3] | {1,2,3}
(1 行记录)
-- 数据中不存在record定义的属性时，默认返回null
demo=# SELECT * FROM jsonb_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int,b text, c text[], d text);
a | b | c | d
---+-----------+---------+---
1 | [1, 2, 3] | {1,2,3} |
(1 行记录)
-- 从表字段中解析数据
demo=# select jt.jsonrecord , x.* from jsontable jt , jsonb_to_record(jt.jsonrecord) as x(a int , b text , c text) ;
jsonrecord | a | b | c
-----------------------------+---+------+----
{"a":1,"b":"bcol","c":"cc"} | 1 | bcol | cc
{"a":1,"b":"bcol","c":""} | 1 | bcol |
{"a":1,"b":"bcol","d":"dd"} | 1 | bcol |
(3 行记录)
JSONB_TO_RECORDSET
功能：
JSON函数，从一个JSON对象数组构建一个任意的记录集合，正如所有返回 record 的函数一样，调用者必须用一个 AS 子句显式的定义记录的结构。
用法：
jsonb_to_recordset(jsonb)
示例：
-- 解析json数据
demo=# SELECT * FROM jsonb_to_recordset ('[{"a":1,"b":"foo","c":true},{"a":"2","b":"bar"}]')as x(a int, b text);
a | b
---+-----
1 | foo
2 | bar
(2 行记录)
-- 数据value的类型需要能够隐式转换到record的匹配类型中
demo=# SELECT * FROM jsonb_to_recordset ('[{"a":1,"b":"foo","c":true},{"a":"2","b":"bar"}]')as x(a int, b text,c int);
错误: 无效的类型 integer 输入语法: "true"
demo=# SELECT * FROM jsonb_to_recordset ('[{"a":1,"b":"foo","c":true},{"a":"2","b":"bar"}]')as x(a int, b text,c boolean);
a | b | c
---+-----+---
1 | foo | t
2 | bar |
(2 行记录)
-- 数据中不存在record定义的属性时，默认返回null
demo=# SELECT * FROM jsonb_to_recordset ('[{"a":1,"b":"foo","c":true},{"a":"2","b":"bar"}]')as x(a int, b text,c boolean,d int);
a | b | c | d
---+-----+---+---
1 | foo | t |
2 | bar | |
(2 行记录)
-- 从表字段中解析数据
demo=# select jt.jsonset , x.* from jsontable jt , jsonb_to_recordset(jt.jsonset) as x(a int , b text , c text) ;
jsonset | a | b | c
-------------------------------------------------------------------+---+------+--------
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol | cc
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_1
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_2
(6 行记录)
JSON_TO_RECORD
功能：
JSON函数，从一个JSON对象构建一个任意的记录，正如所有返回 record 的函数一样，调用者必须用一个 AS 子句显式的定义记录的结构。
用法：
json_to_record(jsonb)
示例：
参照JSONB_TO_RECORD使用示例
JSON_TO_RECORDSET
功能：
JSON函数，从一个JSON对象数组构建一个任意的记录集合，正如所有返回 record 的函数一样，调用者必须用一个 AS 子句显式的定义记录的结构。
用法：
json_to_recordset(jsonb)
示例：
参照JSONB_TO_RECORDSET使用示例