KingbaseES Json 系列七：Json记录操作函数二
KingbaseES Json 系列七--Json记录操作函数二(JSONB_POPULATE_RECORD,JSONB_POPULATE_RECORDSET,JSON_POPULATE_RECORD,JSON_POPULATE_RECORDSET)
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_POPULATE_RECORD
功能：
JSON函数，扩展 from_json 中的对象成一个行，它的列匹配由 base 定义的记录类型。在 JSONB 对象的那些与自定义类型中的列名匹配的字段，他们的值将被插入到对应的输出的列中。而 JSONB 对象中的那些没有匹配到自定类型中的列名的字段将会被忽略。
用法：
jsonb_populate_record(base anyelement,from_json jsonb)
示例：
-- 创建自定义的 SQL 类型
CREATE TYPE recordtype as (a INT, b text, c text);
--解析json数据
demo=# SELECT * FROM jsonb_populate_record(NULL::recordtype , '{"a":1,"b":"bcol","c":"c_text"}');
a | b | c
---+------+--------
1 | bcol | c_text
(1 行记录)
-- 数据value的类型需要能够隐式转换到record的匹配类型中
demo=# SELECT * FROM jsonb_populate_record(NULL::recordtype , '{"a":"a","b":"bcol","c":""}');
错误: 无效的类型 integer 输入语法: "a"
demo=# SELECT * FROM jsonb_populate_record(NULL::recordtype , '{"a":"1","b":"bcol","c":""}');
a | b | c
---+------+---
1 | bcol |
(1 行记录)
-- 数据中不存在record定义的属性时，默认返回null
demo=# SELECT * FROM jsonb_populate_record(NULL::recordtype , '{"a":1,"b":"bcol"}');
a | b | c
---+------+---
1 | bcol |
(1 行记录)
-- 数据中多余的属性时，默认忽略
demo=# SELECT * FROM jsonb_populate_record(NULL::recordtype , '{"a":1,"b":"bcol","c":"c_text","d":"dcol"}');
a | b | c
---+------+--------
1 | bcol | c_text
(1 行记录)
-- 通过设置record转换类型，设置数据不存在时默认值
demo=# SELECT * FROM jsonb_populate_record((0 , 'b_default' , 'not exist' )::recordtype , '{"a":1,"b":"bcol"}');
a | b | c
---+------+-----------
1 | bcol | not exist
(1 行记录)
-- 从表字段中解析数据
demo=# SELECT jt.jsonrecord ,jpr.* FROM jsontable jt , jsonb_populate_record(NULL::recordtype , jt.jsonrecord) jpr;
jsonrecord | a | b | c
-----------------------------+---+------+----
{"a":1,"b":"bcol","c":"cc"} | 1 | bcol | cc
{"a":1,"b":"bcol","c":""} | 1 | bcol |
{"a":1,"b":"bcol","d":"dd"} | 1 | bcol |
(3 行记录)
-- 通过设置record转换类型，设置数据不存在时默认值
demo=# SELECT jt.jsonrecord ,jpr.* FROM jsontable jt , jsonb_populate_record((0 , 'not exist' , 'not exist')::recordtype , jt.jsonrecord) jpr;
jsonrecord | a | b | c
-----------------------------+---+------+-----------
{"a":1,"b":"bcol","c":"cc"} | 1 | bcol | cc
{"a":1,"b":"bcol","c":""} | 1 | bcol |
{"a":1,"b":"bcol","d":"dd"} | 1 | bcol | not exist
(3 行记录)
JSONB_POPULATE_RECORDSET
功能：
JSON函数，扩展 from_json 中最外层的对象数组成一个集合，它的列匹配由 base 定义的记录类型。
用法：
jsonb_populate_recordset(base anyelement,from_json jsonb)
示例：
CREATE TYPE recordtype as (a INT, b text, c text);
-- 直接解析json数据
demo=# SELECT * FROM jsonb_populate_recordset(NULL::recordtype , '[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","c":"c_dd"}]');
a | b | c
---+------+------
1 | bcol | cc
1 | bcol | c_dd
(2 行记录)
-- 数据value的类型需要能够隐式转换到record的匹配类型中
demo=# SELECT * FROM jsonb_populate_recordset(NULL::recordtype , '[{"a":"a","b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
错误: 无效的类型 integer 输入语法: "a"
demo=# SELECT * FROM jsonb_populate_recordset(NULL::recordtype , '[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
a | b | c
---+------+----
1 | bcol | cc
1 | bcol |
(2 行记录)
-- 数据中不存在record定义的属性时，默认返回null
-- 数据中多余的属性时，默认忽略
demo=# SELECT * FROM jsonb_populate_recordset(NULL::recordtype , '[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
a | b | c
---+------+----
1 | bcol | cc
1 | bcol |
(2 行记录)
-- 通过设置record转换类型，设置数据不存在时默认值
demo=# SELECT * FROM jsonb_populate_recordset((0 , 'b_default' , 'not exist' )::recordtype , '[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]');
a | b | c
---+------+-----------
1 | bcol | cc
1 | bcol | not exist
(2 行记录)
-- 从表字段中解析数据
demo=# SELECT jt.jsonset ,jpr.* FROM jsontable jt , jsonb_populate_recordset(NULL::recordtype , jt.jsonset) jpr;
jsonset | a | b | c
-------------------------------------------------------------------+---+------+--------
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol | cc
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_1
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_2
(6 行记录)
-- 通过设置record转换类型，设置数据不存在时默认值
demo=# SELECT jt.jsonset ,jpr.* FROM jsontable jt , jsonb_populate_recordset((0 , 'not exist' , 'not exist')::recordtype , jt.jsonset) jpr;
jsonset | a | b | c
-------------------------------------------------------------------+---+------+-----------
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol | cc
[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}] | 1 | bcol | not exist
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol |
[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}] | 1 | bcol | not exist
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_1
[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}] | 1 | bcol | cc_3_2
(6 行记录)
JSON_POPULATE_RECORD
功能：
JSON函数，扩展 from_json 中的对象成一个行，它的列匹配由 base 定义的记录类型。
用法：
json_populate_record(base anyelement,from_json jsonb)
示例：
参照JSONB_POPULATE_RECORD使用示例
JSON_POPULATE_RECORDSET
功能：
JSON函数，扩展 from_json 中最外层的对象数组成一个集合，它的列匹配由 base 定义的记录类型。
用法：
json_populate_record(base anyelement,from_json jsonb)
示例：
参照JSONB_POPULATE_RECORDSET使用示例
KINGBASE研究院
分类:
SQL
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！