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 行记录)
