KingbaseES Json 系列九--Json路径查询函数(JSONB_PATH_EXISTS,JSONB_PATH_MATCH,JSONB_PATH_QUERY,JSONB_PATH_QUERY_ARRAY,JSONB_PATH_QUERY_FIRST)
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_PATH_EXISTS
功能：
JSON函数，检查JSON路径是否返回指定JSON值的任何项。
用法：
jsonb_path_exists(target jsonb, path jsonpath[, vars jsonb [, silent bool]])
示例：
demo=# select jsonb_path_exists('{"a": [1, 2, 3, 4, 5]}'::jsonb ,'$.a') ;
jsonb_path_exists
-------------------
t
(1 行记录)
demo=# select jsonb_path_exists('{"a": [1, 2, 3, 4, 5]}'::jsonb ,'$.a[*] ?(@ >= $min && @ <= $max)', '{"min":2,"max":4}') ;
jsonb_path_exists
-------------------
t
(1 行记录)
JSONB_PATH_MATCH
功能：
JSON函数，返回指定JSON值的JSON路径谓词检查的结果。只考虑结果的第一项。如果结果不是布尔值，则返回NULL.
用法：
jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
示例：
demo=# select jsonb_path_match('{"a": [1, 2, 3, 4, 5]}'::jsonb ,'$.a[0]') ;
错误: 应为单个布尔结果
demo=# select jsonb_path_match('{"a":true}'::jsonb ,'$.a') ;
jsonb_path_match
------------------
t
(1 行记录)
demo=# select jsonb_path_match('{"a":0}'::jsonb ,'$.a') ;
错误: 应为单个布尔结果
demo=# select jsonb_path_match('{"a": [1, 2, 3, 4, 5]}'::jsonb ,'exists($.a)');
jsonb_path_match
------------------
t
(1 行记录)
demo=# select jsonb_path_match('{"a": [1, 2, 3, 4, 5]}'::jsonb ,'exists($.a[*] ?(@ >= $min && @ <= $max))', '{"min":2,"max":4}') ;
jsonb_path_match
------------------
t
(1 行记录)
JSONB_PATH_QUERY
功能：
JSON函数返回一个 JSONB 值的集合，它包含了在指定的 JSON 值中所有与指定的路径匹配的值。
用法：
jsonb_path_query(targetjsonb, path jsonpath [, vars jsonb [, silent bool]])
示例：
demo=# select jsonvarchar,jsonb_path_query(jsonvarchar::jsonb ,'$') from jsontable ;
jsonvarchar | jsonb_path_query
---------------------------------------------------------+---------------------------------------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} | {"f2": {"f3": 1}, "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, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}
(3 行记录)
demo=# select jsonvarchar,jsonb_path_query(jsonvarchar::jsonb ,'$.a') from jsontable ;
jsonvarchar | jsonb_path_query
---------------------------------------------------------+------------------
{"a": [1, 2, 3, 4, 5]} | [1, 2, 3, 4, 5]
{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}} | 1
(2 行记录)
demo=# select jsonvarchar,jsonb_path_query(jsonvarchar::jsonb ,'$.a[*]') from jsontable ;
jsonvarchar | jsonb_path_query
---------------------------------------------------------+------------------
{"a": [1, 2, 3, 4, 5]} | 1
{"a": [1, 2, 3, 4, 5]} | 2
{"a": [1, 2, 3, 4, 5]} | 3
{"a": [1, 2, 3, 4, 5]} | 4
{"a": [1, 2, 3, 4, 5]} | 5
{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}} | 1
(6 行记录)
demo=# select jsonvarchar,jsonb_path_query(jsonvarchar::jsonb ,'$.a[*] ? (@ >= $min && @ <= $max)','{"min":2,"max":4}') from jsontable ;
jsonvarchar | jsonb_path_query
------------------------+------------------
{"a": [1, 2, 3, 4, 5]} | 2
{"a": [1, 2, 3, 4, 5]} | 3
{"a": [1, 2, 3, 4, 5]} | 4
(3 行记录)
JSONB_PATH_QUERY_ARRAY
功能：
JSON函数，获取指定JSON值的JSON路径返回的所有项，并将结果包装到数组中。
用法：
jsonb_path_query_array(target jsonb, path jsonpath[, vars jsonb [, silent bool]])
示例：
demo=# select jsonvarchar,jsonb_path_query_array(jsonvarchar::jsonb ,'$') from jsontable ;
jsonvarchar | jsonb_path_query_array
---------------------------------------------------------+-----------------------------------------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} | [{"f2": {"f3": 1}, "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, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}]
(3 行记录)
demo=# select jsonvarchar,jsonb_path_query_array(jsonvarchar::jsonb ,'$.a') from jsontable ;
jsonvarchar | jsonb_path_query_array
---------------------------------------------------------+------------------------
{"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 jsonvarchar,jsonb_path_query_array(jsonvarchar::jsonb ,'$.a[*]') from jsontable ;
jsonvarchar | jsonb_path_query_array
---------------------------------------------------------+------------------------
{"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 jsonvarchar,jsonb_path_query_array(jsonvarchar::jsonb ,'$.a[*] ? (@ >= $min && @ <= $max)','{"min":2,"max":4}') from jsontable ;
jsonvarchar | jsonb_path_query_array
---------------------------------------------------------+------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} | []
{"a": [1, 2, 3, 4, 5]} | [2, 3, 4]
{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}} | []
(3 行记录)
JSONB_PATH_QUERY_FIRST
功能：
JSON函数，获取指定JSON值的JSON路径返回的第一个JSON项。在没有结果时返回NULL。
用法：
jsonb_path_query_first(targetjsonb, path jsonpath [, vars jsonb [, silent bool]])
示例：
demo=# select jsonvarchar,jsonb_path_query_first(jsonvarchar::jsonb ,'$') from jsontable ;
jsonvarchar | jsonb_path_query_first
---------------------------------------------------------+---------------------------------------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} | {"f2": {"f3": 1}, "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, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}
(3 行记录)
demo=# select jsonvarchar,jsonb_path_query_first(jsonvarchar::jsonb ,'$.a') from jsontable ;
jsonvarchar | jsonb_path_query_first
---------------------------------------------------------+------------------------
{"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 jsonvarchar,jsonb_path_query_first(jsonvarchar::jsonb ,'$.a[*]') from jsontable ;
jsonvarchar | jsonb_path_query_first
---------------------------------------------------------+------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} |
{"a": [1, 2, 3, 4, 5]} | 1
{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}} | 1
(3 行记录)
demo=# select jsonvarchar,jsonb_path_query_first(jsonvarchar::jsonb ,'$.a[*] ? (@ >= $min && @ <= $max)','{"min":2,"max":4}') from jsontable ;
jsonvarchar | jsonb_path_query_first
---------------------------------------------------------+------------------------
{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} |
{"a": [1, 2, 3, 4, 5]} | 2
{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}} |
(3 行记录)