在数据处理中，JSON以其灵活的结构备受青睐。KingbaseES提供强大的JSON处理能力，支持JSON和JSONB两种类型。本文将深入解析三大类JSON操作函数，助你轻松玩转半结构化数据！

一、基础记录操作函数

1. 单JSON对象转记录

• 函数：JSONB_TO_RECORD / JSON_TO_RECORD
• 功能：将单个JSON对象解析为数据库记录，需显式定义字段类型。
• 场景：快速提取JSON中的关键字段。

SELECT * FROM jsonb_to_record('{"id":1,"name":"Kingbase"}') 
AS t(id INT, name TEXT);
-- 结果：id | name
--       1  | Kingbase

• 避坑指南：字段类型需匹配，否则转换失败。未定义的字段返回NULL。

2. JSON数组转记录集

• 函数：JSONB_TO_RECORDSET / JSON_TO_RECORDSET
• 功能：解析JSON数组为多行记录，适合批量处理。

SELECT * FROM jsonb_to_recordset('[{"score":90},{"score":85}]') 
AS t(score INT);
-- 结果：score
--        90
--        85

3. 动态填充记录

• 函数：JSONB_POPULATE_RECORD
• 亮点：支持预定义类型和默认值，自动忽略多余字段。

CREATE TYPE user_type AS (age INT, city TEXT);
SELECT jsonb_populate_record(
  (NULL::user_type), 
  '{"age":25,"city":"北京","tel":"10086"}'
);
-- 结果：age | city
--        25 | 北京

二、高级表格解析：JSON_TABLE

五大核心功能

1. 值提取
   直接映射JSON字段到列：

SELECT * FROM JSON_TABLE( '{"dept":"IT","staff":[{"name":"王强"},{"name":"李娜"}]}', '$.staff[*]' COLUMNS ( name TEXT PATH '$.name' ) ); -- 结果：name -- 王强 -- 李娜

2. 存在性检查
   快速判断字段是否存在：

SELECT * FROM JSON_TABLE( '{"email":"support@kingbase.com"}', '#39; COLUMNS ( has_phone BOOLEAN EXISTS PATH '$.phone' ) ); -- 结果：has_phone → false

3. 嵌套展开
   多层JSON一键扁平化：

SELECT * FROM JSON_TABLE( '{"order_id":1001,"items":[{"product":"A","qty":2}]}', '#39; COLUMNS ( order_id INT PATH '$.order_id', NESTED PATH '$.items[*]' COLUMNS ( product TEXT PATH '$.product', qty INT PATH '$.qty' ) ) ); -- 结果：order_id | product | qty -- 1001 | A | 2

4. 连接策略
   通过PLAN子句控制数据关联方式：

PLAN (OUTER (UNION)) -- 左外连接+并集 PLAN (INNER (CROSS)) -- 内连接+笛卡尔积

三、实战技巧

性能优化

• 优先使用JSONB：二进制存储，支持索引，查询更快。
• 路径表达式优化：避免使用通配符**，明确层级路径。

常见误区

-- 错误：类型不匹配
SELECT jsonb_to_record('{"price":"99.5"}') AS t(price INT);
-- 正确：字符串→数字需显式转换
SELECT jsonb_to_record('{"price":"99.5"}') AS t(price NUMERIC);

四、总结

掌握这些函数，无论是日志解析、配置管理还是接口数据存储，都能游刃有余！立即体验KingbaseES，释放JSON数据潜能吧！

（注：本文示例基于KingbaseES V9，实际使用请参考最新版本文档）

Tags：jsonpath解析json