KingbaseES JSON函数全解析：构建与转换实战指南

引言
在大数据时代，JSON已成为数据交换的主流格式。KingbaseES作为国产数据库佼佼者，提供了丰富的JSON处理函数。本文将深度解析JSON构造函数与对象函数，助你轻松玩转半结构化数据存储！

---

一、环境准备

快速搭建测试环境，建议收藏备用：

-- 创建测试表
CREATE TABLE jsontable (id INT, jsondata JSON,jsonvarchar VARCHAR,jsonarray JSON
);-- 插入示例数据
INSERT INTO jsontable 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"]'),
(2, '{"a":[1,2,3,4,5]}', '{"a":[1,2,3,4,5]}', '[1,2,3,4,5]'),
(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]');

---

二、构造函数详解

1. JSON() 函数

功能：将字符串转换为JSON类型
特性：

• 支持UTF8编码的文本或二进制数据
• 支持控制重复键的校验（WITH/WITHOUT UNIQUE KEYS）

-- 基础转换
SELECT JSON('[1,2,3]'); → [1,2,3]-- 字段转换示例
SELECT JSON(jsonvarchar) FROM jsontable WHERE id=1;
-- 结果：{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}

2. ROW_TO_JSON()

功能：将行记录转为JSON对象
参数：
pretty_bool：True时自动美化格式

-- 单行转换
SELECT row_to_json(row(1,'foo')); 
-- {"f1":1,"f2":"foo"}-- 整表转换（美化输出）
SELECT row_to_json(row(id,jsonvarchar), true) FROM jsontable;
/* 输出：
{"f1": 1,"f2": "{\"f2\":{\"f3\":1},\"f4\":{\"f5\":99,\"f6\":\"foo\"}}"
}
*/

3. TO_JSON/TO_JSONB

核心区别：

• JSONB会优化存储格式并支持索引
• JSON保持原始文本格式

SELECT to_jsonb('{"a":1}'::JSON); → {"a": 1}
SELECT to_json(array['a','b']);   → ["a","b"]

---

三、对象构造神器

1. JSONB_BUILD_OBJECT()

功能：动态构建JSON对象
注意：参数必须成对出现（键值交替）

-- 从字段动态构建
SELECT jsonb_build_object(id, name) FROM comtable;
/* 输出：
{"1": "a"}
{"2": "b"}
{"3": "c"}
*/

2. JSONB_OBJECT()

两种用法：
① 单数组模式：jsonb_object(ARRAY[key1,val1,key2,val2])
② 双数组模式：jsonb_object(keys_array, values_array)

-- 单数组示例
SELECT jsonb_object(ARRAY[id::text, name::text]) FROM comtable;-- 双数组示例
SELECT jsonb_object(ARRAY['id','name'], ARRAY['1','测试']);
-- 结果：{"id": "1", "name": "测试"}

---

四、最佳实践建议

1. ​存储选择：优先使用JSONB类型，支持索引且处理效率更高
2. ​数据校验：生产环境建议使用WITH UNIQUE KEYS避免重复键
3. ​性能优化：复杂JSON操作尽量在数据库层完成，减少数据传输
4. ​格式转换：使用TO_JSONB()可将XML/CSV等其他格式统一转为JSONB

---

五、实战场景

场景：构建用户信息嵌套JSON

SELECT jsonb_build_object('user_id', id,'profile', jsonb_build_object('name', name,'attributes', jsonb_object(ARRAY['role','department'], ARRAY['admin','IT']))
) FROM comtable;
/* 输出：
{"user_id": 1,"profile": {"name": "a","attributes": {"role": "admin", "department": "IT"}}
}
*/