KingbaseES Json 系列四--Json数据操作函数二(JSONB_PRETTY,JSONB_STRIP_NULLS,JSON_OBJECTAGG,JSON_EQUAL,JSON_EXISTS,JSON_STRIP_NULLS)
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_PRETTY
功能：
JSON函数，把 from_json 处理成一段带缩进的JSON文本。
用法：
jsonb_pretty(from_json jsonb)
示例：
demo=# SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');
jsonb_pretty
--------------------
[ +
{ +
"f1": 1, +
"f2": null+
}, +
2, +
null, +
3 +
]
(1 行记录)
demo=# select jsonb_pretty(jsonset) from jsontable where id = 1;;
jsonb_pretty
----------------------
[ +
{ +
"a": 1, +
"b": "bcol",+
"c": "cc" +
}, +
{ +
"a": 1, +
"b": "bcol",+
"d": "" +
} +
]
(1 行记录)
JSONB_STRIP_NULLS
功能：
JSON函数，返回 from_json ，其中所有具有空值的对象域都被过滤掉，其他空值不动。
用法：
jsonb_strip_nulls(from_json jsonb)
示例：
demo=# SELECT jsonb_strip_nulls(jsonarray),jsonarray from jsontable where id = 3;;
jsonb_strip_nulls | jsonarray
-------------------------+-------------------------------
[{"f1": 1}, 2, null, 3] | [{"f1":1,"f2":null},2,null,3]
(1 行记录)
JSON_OBJECTAGG
功能：
聚集函数，把键值对聚集成一个JSON对象。
用法1：
json_objectagg(
[ { key_expression { VALUE | ':' } value_expression } ]
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
)
key_expression { VALUE | ':' } value_expression ：输入的数据聚集为一个JSON对象。
{ NULL | ABSENT } ON NULL：指定函数在key_expression计算结果为null时的行为。
{ WITH | WITHOUT } UNIQUE [ KEYS ]：定义是否允许重复键。
RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ]]：指定生成数据的类型。支持以下类型：json、jsonb、bytea和字符串类型（text、char、varchar和nchar），还支持VARCHAR2、BLOB、CLOB类型。
示例1：
-- 常用方式查询
demo=# select json_objectagg(id : name) from comtable ;
json_objectagg
-------------------------------------
{ "1" : "a", "2" : "b", "3" : "c" }
(1 行记录)
demo=# select json_objectagg(id value name) from comtable ;
json_objectagg
-------------------------------------
{ "1" : "a", "2" : "b", "3" : "c" }
(1 行记录)
-- json对象key字段不能为空
demo=# insert into comtable values('','a_1');
INSERT 0 1
demo=# select json_objectagg(id value name) from comtable ;
错误: 字段名不能为空
-- 重复key值限制
demo=# insert into comtable values('4','');
INSERT 0 1
demo=# select json_objectagg(id value name ) from comtable ;
json_objectagg
------------------------------------------------
{ "1" : "a", "2" : "b", "3" : "c", "1" : "d" }
(1 行记录)
demo=# select json_objectagg(id value name with unique keys) from comtable ;
错误: duplicate JSON key "1"
用法2：
json_objectagg(
[ { key_expression { VALUE | ':' } value_expression } ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
)
-- 与用法1区别：returning子句与with子句位置可互换。
示例2：
demo=# select json_objectagg(id value name returning jsonb with unique keys ) from comtable;
json_objectagg
--------------------------------
{"1": "a", "2": "b", "3": "c"}
(1 行记录)
JSON_EQUAL
功能：
JSON处理函数，用于判断输入的两个文本值是否相等，前提是两个文本必须是有效的JSON值，否则返回false，当都为有效JSON值且相等则返回true，否则返回false。
用法：
json_equal( expr ,expr [{error|false|true} on error ])
expr：输入参数，可以是列名、字符串常量、复合表达式和函数表达式，数据类型必须是
字符类型变量（character [(n [char |byte])], character varying[(n [char |byte])],text,clob,blob）
{error|false|true} on error：发生错误时，默认返回true或者false。
示例：
demo=# SELECT json_equal('{"a":1}','{"a":1}') FROM dual;
json_equal
------------
t
(1 行记录)
demo=# SELECT json_equal('{"a":1}','{"a":"1"}') FROM dual;
json_equal
------------
f
(1 行记录)
demo=# SELECT json_equal('{"a":1}','{"a": 01}') FROM dual;
json_equal
------------
t
(1 行记录)
-- 设置json解析失败的默认返回值
demo=# SELECT json_equal('{"a":1}','sdfasdf' false on error) FROM dual;
json_equal
------------
f
(1 行记录)
JSON_EXISTS
功能：
使用表达式JSON_EXISTS用于从一个输入的json文本中检索指定的jsonpath路径是否存在指定的JSON值,如果存在返回true，不存在返回false。
用法：
json_exists(
context_item, path_expression
[ PASSING { value AS varname }[, ...]]
[ RETURNING data_type ]
[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
)
context_item:输入表达式
path_expression：路径表达式
[ PASSING { value AS varname }[, ...]]:使用此子句将值传递给路径表达式
[ RETURNING data_type ]:此子句指定返回的数据类型
[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]:当未找到匹配值时，使用此子句返回指定的值,默认FALSE
示例：
demo=# select jsondata , json_exists(jsondata:: jsonb , '$.a') from jsontable ;
jsondata | json_exists
------------------------------------------------------+-------------
{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}} | f
{"a":[1,2,3,4,5]} | t
{"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | t
(3 行记录)
-- path_expression模式可以选择指定关键字 lax 或 strict来声明路径模式。 默认值为 lax。
-- 在 lax 模式下，如果路径表达式包含错误，函数将返回空值。 例如，如果请求值 $.name，但 JSON 文本不包含 name 键，函数将返回 null，但不会引发错误。
-- 在 strict 模式下，如果路径表达式包含错误，函数将引发错误。
demo=# select jsondata , json_exists(jsondata:: jsonb , 'strict $.a' ERROR ON ERROR) from jsontable ;
错误: JSON对象不包含键"a"
demo=# select jsondata , json_exists(jsondata:: jsonb , 'lax $.a' ERROR ON ERROR) from jsontable ;
jsondata | json_exists
------------------------------------------------------+-------------
{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}} | f
{"a":[1,2,3,4,5]} | t
{"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}} | t
(3 行记录)
JSON_STRIP_NULLS
功能：
JSON函数，返回 from_json ，其中所有具有空值的对象域都被过滤掉，其他空值不动。
用法：
json_strip_nulls(from_json json)
示例：
参照JSONB_STRIP_NULLS使用示例