金仓数据库KingbaseES Json 系列三：Json数据操作函数一

数据猿 2023-04-13

755

####### KingbaseES Json 系列三–Json数据操作函数一
(JSONB_EACH,JSONB_EACH_TEXT,JSONB_OBJECT_KEYS,JSONB_EXTRACT_PATH,JSONB_EXTRACT_PATH_TEXT,JSON_EACH,JSON_EACH_TEXT,JSON_OBJECT_KEYS,JSON_EXTRACT_PATH,JSON_EXTRACT_PATH_TEXT)

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函数简介

JSONB_EACH

功能：

JSON处理函数，扩展最外层的JSON对象成为一组键值对。

用法：

jsonb_each(jsonb)

示例：

demo=# SELECT jt.jsondata,je.* FROM jsontable jt,  jsonb_each(jt.jsondata) je;
                       jsondata                       | key |          value          
------------------------------------------------------+-----+-------------------------
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f2  | {"f3": 1}
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f4  | {"f5": 99, "f6": "foo"}
 {"a":[1,2,3,4,5]}                                    | a   | [1, 2, 3, 4, 5]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | a   | 1
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | b   | ["2", "a b"]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | c   | {"d": 4, "e": "ab c"}
(6 行记录)

JSONB_EACH_TEXT

功能：

JSON处理函数，扩展最外层的JSON对象成为一组键值对，返回值为 text 类型。

用法：

jsonb_each_text(jsonb)

示例：

demo=# SELECT jt.jsondata,je.* FROM jsontable jt,  jsonb_each_text(jt.jsondata) je;
                       jsondata                       | key |          value          
------------------------------------------------------+-----+-------------------------
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f2  | {"f3": 1}
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f4  | {"f5": 99, "f6": "foo"}
 {"a":[1,2,3,4,5]}                                    | a   | [1, 2, 3, 4, 5]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | a   | 1
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | b   | ["2", "a b"]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | c   | {"d": 4, "e": "ab c"}
(6 行记录)

JSONB_OBJECT_KEYS

功能：

JSON函数，返回外层JSON对象中键的集合。

用法：

jsonb_object_keys(jsonb)

示例：

demo=#  select jt.jsondata,  jo.* from  jsontable jt,  jsonb_object_keys(jt.jsondata) jo;
                       jsondata                       | jo 
------------------------------------------------------+----
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f2
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | f4
 {"a":[1,2,3,4,5]}                                    | a
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | a
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | b
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | c
(6 行记录)

JSON_EXTRACT_PATH

功能：

JSON处理函数，返回由 path_elems 指向的JSON值(等效于#>操作符)。

用法：

json_extract_path(from_json json, VARIADIC path_elems text[])

示例：

demo=# select jt.jsondata,  je.* from jsontable jt,  json_extract_path(jt.jsondata ,'a') je;
                       jsondata                       |     je      
------------------------------------------------------+-------------
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | 
 {"a":[1,2,3,4,5]}                                    | [1,2,3,4,5]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | 1
(3 行记录)

-- 多个路径提取子对象中的值

demo=# select jt.jsondata,  je.* from jsontable jt,  json_extract_path(jt.jsondata ,'f4' ,'f5') je;
                       jsondata                       | je 
------------------------------------------------------+----
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | 99
 {"a":[1,2,3,4,5]}                                    | 
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | 
(3 行记录)

JSON_EXTRACT_PATH_TEXT

功能：

JSON处理函数，以 text 类型返回由 path_elems 指向的JSON值(等效于#>操作符)。

用法：

json_extract_path_text(from_json json, VARIADIC path_elems text[])

示例：

demo=# select jt.jsondata,  je.* from jsontable jt,  json_extract_path_text(jt.jsondata ,'a') je;
                       jsondata                       |     je      
------------------------------------------------------+-------------
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | 
 {"a":[1,2,3,4,5]}                                    | [1,2,3,4,5]
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | 1
(3 行记录)

-- 多个路径提取子对象中的值

demo=# select jt.jsondata,  je.* from jsontable jt,  json_extract_path_text(jt.jsondata ,'f4' ,'f5') je;
                       jsondata                       | je 
------------------------------------------------------+----
 {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}            | 99
 {"a":[1,2,3,4,5]}                                    | 
 {"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | 
(3 行记录)

JSON_EACH

功能：