Kingbase KES V9R3C15 JSON/JSONB 函数速查：常用操作 + 踩坑总结

Kingbase KES V9R3C15 JSON/JSONB 函数速查：常用操作 + 踩坑总结

> 我在 Kingbase KES V9R3C15 里折腾 JSON/JSONB 一段时间后发现：只要把“构造、提取、更新、校验、聚合”这五类函数吃透，绝大多数业务 JSON 字段都能优雅处理；而 JSONB 在大数据量检索上通常更舒服，能用就优先用。下面按“最常用→最容易踩坑→最能提效”的顺序，给你一份可直接套用的函数清单与例子。

0）使用前必做：启用 mysql_json 插件

很多人第一次运行 JSON_* 函数就报错，原因是没启用扩展。先执行一次（一般只需执行一次）：

CREATE EXTENSION mysql_json;

小提醒：

  • 接收 json_doc 参数的函数，传入的字符串如果不是合法 JSON，往往会直接抛错（不是返回 NULL）。
  • 某些 path 表达式不支持 * 或 ** 这种通配写法，别想着“一把梭”，很容易踩坑（后面会提）。

1）快速构造 JSON：数组与对象

这类函数最适合“把结构拼出来再存库”或“临时返回给接口”。

1.1 JSON_ARRAY：把多个值拼成 JSON 数组

用法：

JSON_ARRAY(value1, value2, ...);

例子：

SELECT JSON_ARRAY(1, 'abc', NULL, TRUE, '10:26:21');
-- 输出: [1,"abc",null,true,"10:26:21"]

1.2 JSON_BUILD_OBJECT：拼 JSON 对象（键值交替）

用法：

JSON_BUILD_OBJECT('key1', val1, 'key2', val2, ...);

例子：

SELECT JSON_BUILD_OBJECT('user', 'Tom', 'score', 98, 'tags', JSON_ARRAY('A','B'));
-- 输出: {"user":"Tom","score":98,"tags":["A","B"]}

1.3 JSON_BUILD_ARRAY：更自由的数组构造

当你希望数组里混合类型（数字、字符串、对象）时更顺手：

SELECT JSON_BUILD_ARRAY(1, '2', TRUE, JSON_BUILD_OBJECT('k', 'v'));

2）提取与展开：查 JSON 字段别再“全取全解析”

当 JSON 字段很大时，最忌讳 SELECT 出来再在应用层解析——数据库能做的就让数据库做。

2.1 JSON_EXTRACT：按路径提取值

用法：

JSON_EXTRACT(json_doc, path)

例子（提取对象字段）：

SELECT JSON_EXTRACT('{"a":1,"b":{"c":2}}', '$.b.c');
-- 输出: 2

例子（提取数组元素）：

SELECT JSON_EXTRACT('{"arr":[10,20,30]}', '$.arr[1]');
-- 输出: 20

> 路径写法要尽量明确：对象用 $.x.y，数组用 $[0] 或 $.arr[0]。别偷懒写模糊路径，后期维护会很痛。

2.2 JSON_ARRAY_ELEMENTS / JSON_ARRAY_ELEMENTS_TEXT：把数组“炸开”

当 JSON 数组需要逐行处理（聚合、过滤、Join）时，这个很香。

SELECT JSON_ARRAY_ELEMENTS('[1,true,null,{"k":1},"x"]');

如果你明确想拿文本值：

SELECT * FROM JSON_ARRAY_ELEMENTS_TEXT('["foo","bar"]');
-- foo
-- bar

3）更新 JSON：INSERT / REPLACE / SET 怎么选？

更新类函数是最容易用混的一组。我的经验是：先搞清楚“路径不存在时你希望发生什么”。

3.1 JSON_INSERT：只在路径不存在时插入（更保守）

场景： 只想补默认值，不想覆盖已有值。

SELECT JSON_INSERT('{"a":1}', '$.b', 2);
-- 输出: {"a":1,"b":2}

3.2 JSON_REPLACE：只在路径存在时替换（更严格）

场景： 只允许改已有字段，字段不存在就别动。

SELECT JSON_REPLACE('{"a":1}', '$.a', 99);
-- 输出: {"a":99}

3.3 JSON_SET：存在就改，不存在就加（最“万能”）

场景： 你就想“写入即生效”，不想做太多判断。

SELECT JSON_SET('{"a":1}', '$.a', 9, '$.b', 2);
-- 输出: {"a":9,"b":2}

> 三句话记忆法：INSERT 不覆盖、REPLACE 不新增、SET 全都行。

4）数组追加与插入：JSON_ARRAY_APPEND / JSON_ARRAY_INSERT

4.1 JSON_ARRAY_APPEND：给数组末尾追加

SELECT JSON_ARRAY_APPEND('[1,2,3]', '$', 4);
-- 输出: [1,2,3,4]

如果路径选到的是标量/对象，系统可能会把它“包成数组”再追加（这点很容易让结果变形），所以我建议：追加前确保目标路径就是数组。

4.2 JSON_ARRAY_INSERT：在指定位置插入

SELECT JSON_ARRAY_INSERT('["a","b","c"]', '$[1]', 'X');
-- 输出: ["a","X","b","c"]

高危踩坑点：

  • path 里带 *、** 通配符的写法，很多实现会直接报错，别抱侥幸心理。

5）校验与美化：JSON_VALID / JSON_PRETTY

这两个是“救命函数”，尤其你在批量导入、外部接口入库时。

5.1 JSON_VALID：判断字符串是不是合法 JSON

SELECT JSON_VALID('{"a":1}');   -- true
SELECT JSON_VALID('{a:1}');     -- false

5.2 JSON_PRETTY：格式化输出，排查结构一眼看清

SELECT JSON_PRETTY('{"a":1,"b":[2,3],"c":{"d":4}}');

> 调试建议：复杂 JSON 先 JSON_PRETTY 看结构，再写 JSON_EXTRACT 的路径，效率会高很多。

6）聚合能力：JSON_AGG / JSON_ARRAYAGG（把多行变数组）

当你要把多行查询结果打包成 JSON 返回给 API，这是最干净的做法之一。

示意：

-- 假设表 t(id, name)
SELECT JSON_AGG(name) FROM t;
-- 输出: ["Alice","Bob","Cindy"]

如果你想聚合对象，也可以先拼对象再聚合：

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) FROM t;

7）包含关系：JSON_CONTAINS / JSON_CONTAINS_PATH

7.1 JSON_CONTAINS：candidate 是否是 target 的子集

SELECT JSON_CONTAINS('{"a":1,"b":2}', '1', '$.a');  -- true
SELECT JSON_CONTAINS('{"a":1,"b":2}', '1', '$.b');  -- false

7.2 JSON_CONTAINS_PATH：只判断路径是否存在

SELECT JSON_CONTAINS_PATH('{"a":1,"b":{"c":2}}', 'one', '$.a', '$.x'); -- true
SELECT JSON_CONTAINS_PATH('{"a":1,"b":{"c":2}}', 'all', '$.a', '$.x'); -- false

> 如果你只是检查“有没有这个字段”，别用 CONTAINS 去比值，CONTAINS_PATH 更轻更直观。[1])

8）辅助信息：长度与深度（做数据体检很有用）

8.1 JSON_ARRAY_LENGTH：最外层数组长度

SELECT JSON_ARRAY_LENGTH('[1,2,3,{"k":1},4]');
-- 输出: 5

8.2 JSON_DEPTH：JSON 最大嵌套深度

SELECT JSON_DEPTH('{"a":[1,[2,3],{"b":4}]}');

什么时候用？

  • 你接外部接口入库，想限制 JSON 深度，防止“超深嵌套”拖垮解析。
  • 你要做“结构复杂度分级”，比如深度>6 的记录进入人工审核队列。

9）我踩过的 5 个坑（看完少走弯路）

  1. 忘了装扩展：先 CREATE EXTENSION mysql_json;，不然你会以为数据库坏了。
  2. path 太随意：路径写得越“魔法”，越难维护；先 PRETTY 再 EXTRACT。
  3. 更新函数混用：INSERT/REPLACE/SET 先想清楚“是否覆盖/是否新增”。
  4. 数组追加变形：目标不是数组时，APPEND 可能自动包数组，结果很怪。
  5. 通配符幻想：别指望用 *、** 一步定位所有节点，很多实现直接报错。

结语：一套“最常用组合拳”

如果你只想记住最核心的一套组合，我建议是：

  • 构造：JSON_BUILD_OBJECT / JSON_ARRAY
  • 提取：JSON_EXTRACT
  • 更新：JSON_SET
  • 校验：JSON_VALID
  • 调试：JSON_PRETTY
  • 聚合：JSON_AGG

把这 6 个用熟，Kingbase KES 里的 JSON 数据处理基本就能横着走了。
ForrestadBashar@gmail.com

评论 0