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
-
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函数简介
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
功能：
JSON处理函数，扩展最外层的JSON对象成为一组键值对。
用法：
json_each(json)示例：
参照JSONB_EACH使用示例- JSONB_EACH
JSON_EACH_TEXT
功能：
JSON处理函数，扩展最外层的JSON对象成为一组键值对，返回值为 text 类型。
用法：
json_each_text(json)示例：
参照JSONB_EACH_TEXT使用示例- JSONB_EACH_TEXT
JSON_OBJECT_KEYS
功能：
JSON函数，返回外层JSON对象中键的集合。
用法：
json_object_keys(json)示例：
参照JSONB_OBJECT_KEYS使用示例- JSONB_OBJECT_KEYS
JSONB_EXTRACT_PATH
功能：
JSON处理函数，返回由 path_elems 指向的JSON值(等效于#>操作符)。
用法：
jsonb_extract_path(from_json json, VARIADIC path_elems text[])示例：
参照JSONB_EXTRACT_PATH使用示例- JSONB_EXTRACT_PATH
JSONB_EXTRACT_PATH_TEXT
功能：
JSON处理函数，以 text 类型返回由 path_elems 指向的JSON值(等效于#>操作符)。
用法：
jsonb_extract_path_text(from_json json, VARIADIC path_elems text[])示例：
参照JSONB_EXTRACT_PATH_TEXT使用示例- JSONB_EXTRACT_PATH_TEXT