KingbaseES Json 系列五：Json数据操作函数三(JSONB_SET,JSONB_INSERT,JSON_QUERY)
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函数列表
json函数简介
JSONB_SET
功能：
JSON函数，返回 target 其中由 path 指定的节用 new_value 替换，如果指定的项不存在并且 create_missing 为真（默认为真）则加上 new_value 。正如面向路径的操作符一样，出现在 path 中的负整数表示从JSON数组的末尾开始数
用法：
jsonb_set(target jsonb, path text[],new_value jsonb[, create_missing boolean])
示例：
-- json数组中，path值'{p1，p2}'，p1：表示数组位置；p2：表示具体的key值。
demo=# SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false);
jsonb_set
---------------------------------------------
[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
(1 行记录)
-- 在数组json中，进行value替换
demo=# select jsonarray , jsonb_set(jsonarray::jsonb , '{0}' ,'[9]' ,false) from jsontable ;
jsonarray | jsonb_set
---------------------------------------------------------------+------------------------------------------------------------------------------
[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"] | [[9], true, [1, [2, 3]], null, {"f1": 1, "f2": [7, 8, 9]}, false, "stringy"]
[1,2,3,4,5] | [[9], 2, 3, 4, 5]
[{"f1":1,"f2":null},2,null,3] | [[9], 2, null, 3]
(3 行记录)
demo=# select jsonarray , jsonb_set(jsonarray::jsonb , '{0}' ,'9' ,false) from jsontable ;
jsonarray | jsonb_set
---------------------------------------------------------------+----------------------------------------------------------------------------
[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"] | [9, true, [1, [2, 3]], null, {"f1": 1, "f2": [7, 8, 9]}, false, "stringy"]
[1,2,3,4,5] | [9, 2, 3, 4, 5]
[{"f1":1,"f2":null},2,null,3] | [9, 2, null, 3]
(3 行记录)
demo=# select jsonarray , jsonb_set(jsonarray::jsonb , '{0}' ,'true' ,false) from jsontable ;
jsonarray | jsonb_set
---------------------------------------------------------------+-------------------------------------------------------------------------------
[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"] | [true, true, [1, [2, 3]], null, {"f1": 1, "f2": [7, 8, 9]}, false, "stringy"]
[1,2,3,4,5] | [true, 2, 3, 4, 5]
[{"f1":1,"f2":null},2,null,3] | [true, 2, null, 3]
(3 行记录)
demo=# select jsonarray , jsonb_set(jsonarray::jsonb , '{0}' ,'"a"' ,false) from jsontable ;
jsonarray | jsonb_set
---------------------------------------------------------------+------------------------------------------------------------------------------
[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"] | ["a", true, [1, [2, 3]], null, {"f1": 1, "f2": [7, 8, 9]}, false, "stringy"]
[1,2,3,4,5] | ["a", 2, 3, 4, 5]
[{"f1":1,"f2":null},2,null,3] | ["a", 2, null, 3]
(3 行记录)
-- json对象值替换
demo=# select jsonrecord , jsonb_set(jsonrecord , '{a}' ,'2' ,false ) from jsontable ;
jsonrecord | jsonb_set
-----------------------------+----------------------------------
{"a":1,"b":"bcol","c":"cc"} | {"a": 2, "b": "bcol", "c": "cc"}
{"a":1,"b":"bcol","c":""} | {"a": 2, "b": "bcol", "c": ""}
{"a":1,"b":"bcol","d":"dd"} | {"a": 2, "b": "bcol", "d": "dd"}
(3 行记录)
-- json对象或者所在位置不存在时，忽略值
demo=# SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f3}', '[2,3,4]', false);
jsonb_set
-------------------------------------
[{"f1": 1, "f2": null}, 2, null, 3]
(1 行记录)
demo=# SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{8}', '[2,3,4]', false);
jsonb_set
-------------------------------------
[{"f1": 1, "f2": null}, 2, null, 3]
(1 行记录)
-- 指定的项不存在并且create_missing 为真（默认为真）则加上 new_value
demo=# SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f3}', '[2,3,4]', true);
jsonb_set
------------------------------------------------------
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2, null, 3]
(1 行记录)
JSONB_INSERT
功能：
JSON处理函数，返回被插入了 new_value 的 target 。如果 path 指定的 target 节在一个JSONB数组中， new_value 将被插入到目标之前（ insert_after 默认为false）或者之后（ insert_after 为true）。如果 path 指定的 target 节在一个JSONB对象内，则只有当 target 不存在时才插入 new_value。对于面向路径的操作符来说，出现在 path 中的负整数表示从JSONB数组的末尾开始计数。
用法：
jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])
示例：
-- 插入新值数据
demo=# SELECT JSONB_INSERT('[{"f1":1,"f2":null},2,null,3]', '{0,f3}', '[2,3,4]', false);
JSONB_INSERT
------------------------------------------------------
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2, null, 3]
(1 行记录)
-- 值存在时，不进行替换
demo=# SELECT JSONB_INSERT('[{"f1":1,"f2":null},2,null,3]', '{0,f2}', '[2,3,4]', false);
错误: 不能替换已经存在的键
提示: 尝试使用 jsonb_set 来替换键值。
-- 通过参数insert_after设置插入位置之前或之后
demo=# SELECT JSONB_INSERT('[{"f1":1,"f2":null},2,null,3]', '{1}', '[2,3,4]', false );
JSONB_INSERT
------------------------------------------------
[{"f1": 1, "f2": null}, [2, 3, 4], 2, null, 3]
(1 行记录)
demo=# SELECT JSONB_INSERT('[{"f1":1,"f2":null},2,null,3]', '{1}', '[2,3,4]', true );
JSONB_INSERT
------------------------------------------------
[{"f1": 1, "f2": null}, 2, [2, 3, 4], null, 3]
(1 行记录)
JSON_QUERY
功能：
JSON函数，用于从一个输入的json文本中根据指定的jsonpath路径检索所需的值（可以为标量值，也可以为对象或者数组）。
用法：
json_query (
expression [FORMAT JSON [ENCODING UTF8]],JSON_basic_path_expression
[ PASSING { value AS varname } [, ...]]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ]
[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON EMPTY ]
[{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON ERROR ]
)
expression：输入的json文本，完整的支持可以为字符常量、函数、或者列名（数据类型为clob,blob或者varchar2），V8R6C7版本只支持输入类型为JSONB类型，其他类型后续补充；
FORMAT JSON：是在expression为字符串类型时将expression格式化成json格式。
ENCODING UTF8：指定输入为二进制类型时的字符集。
json_basic_path_expression：用于指定json文件所要查询的路径。
PASSING { value AS varname } [, ...]:将varname变量的值替换成value进行查询，需要注意
a.varname为非限定标识符（不带双引号）时会自动转换为小写，为限定标识符（带双引号）时原样存储不进行大小写转换。
b.varname的数据类型应与检索值相同。
RETURNING data_type：指定返回值的数据类型。
{ WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER：控制JSON_QUERY函数返回值是否用方括号“[]”包围。
a.WITHOUT WRAPPER：默认选项，返回值不用‘[]’包围。
b.WITH WRAPPER:返回值用‘[]’包围，等价于WITH ARRAY WRAPPER。
c.WITH UNCONDITIONAL WRAPPER:等价于WITH WRAPPER.
d.WITH CONDITIONAL WRAPPER：
* 如果匹配到多个值或单个标量值，与WITH WRAPPER相同；
* 如果匹配到单个对象或者数组，与WITHOUT WRAPPER相同。
{ KEEP | OMIT } QUOTES [ ON SCALAR STRING ]:Quotes子句指定当返回值为标量字符串时是否保留双引号（“”）.
a.指定KEEP时保留，此为默认选项；
b.指定OMIT时不保留。
c.QUOTES子句不能与WITH WRAPPER子句共用。
d.ON SCALAR STRING关键字只为语法清晰，添加与否对结果无影响。
{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON ERROR：指定错误发生时的返回值。
a.NULL ON ERROR - 返回NULL,此为默认选项
b.ERROR ON ERROR - 报错
c.EMPTY ON ERROR - 等价于 EMPTY ARRAY ON ERROR.
d.EMPTY ARRAY ON ERROR - 返回空数组 ([])
e.EMPTY OBJECT ON ERROR - 返回空对象 ({})
{ERROR|NULL|EMPTY|EMPTY ARRAY|EMPTY OBJECT|DEFAULT expression } ON EMPTY：指定没有结果相匹配时的返回值。
a.NULL ON EMPTY - 返回NULL,此为默认选项
b.ERROR ON EMPTY - 报错
c.EMPTY ON EMPTY - 等价于 EMPTY ARRAY ON ERROR.
d.EMPTY ARRAY ON EMPTY - 返回空数组 ([])
e.EMPTY OBJECT ON EMPTY - 返回空对象 ({})
示例：
-- JSON_basic_path_expression支持部分函数的使用(size,type,double,ceiling,floor,abs)
demo=# select json_query('[1,2,3,4,5]'::jsonb,'$.size()') ;
json_query
------------
5
(1 行记录)
demo=# select json_query(' {"a":1,"b":"bcol","c":"cc"}'::jsonb,'$.b.type()') ;
json_query
------------
"string"
(1 行记录)
demo=# select json_query(' {"a":-1,"b":"bcol","c":"cc"}'::jsonb,'$.a.abs()') ;
json_query
------------
1
(1 行记录)
-- JSON_basic_path_expression数组数据检索
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a');
json_query
-----------------
[1, 2, 3, 4, 5]
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[2]');
json_query
------------
3
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a.size()');
json_query
------------
5
(1 行记录)
-- JSON_basic_path_expression 数据筛选，只能返回一条数据，多条数据返回空值
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[*] ? ( @ > 4)');
json_query
------------
5
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[*] ? ( @ > 3)');
json_query
------------
(1 行记录)
-- 通过passing传递参数
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[*] ? ( @ > $x)' passing 4 as x);
json_query
------------
5
(1 行记录)
-- 通过returning返回值类型，并设置是否使用方括号“[]”包围（WITH WRAPPER）
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[*] ? ( @ > 4)' returning text);
json_query
------------
5
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a[*] ? ( @ > 4)' returning text WITH WRAPPER);
json_query
------------
[5]
(1 行记录)
-- 指定当返回值为字符串时是否保留双引号（“”）.
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.c.e' KEEP QUOTES);
json_query
------------
"ab c"
(1 行记录)
-- 指定错误发生时的返回值。
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.c.e' returning int null on error);
json_query
------------
(1 行记录)
-- 指定没有结果相匹配时的返回值
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.c.f' EMPTY ARRAY ON EMPTY);
json_query
------------
[]
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.c.f' EMPTY OBJECT ON EMPTY);
json_query
------------
{}
(1 行记录)
demo=# select json_query('{"a": [1, 2, 3, 4, 5], "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.c.f' returning text DEFAULT 'emp' on empty);
json_query
------------
emp
(1 行记录)