用 JSON_TYPE 在触发器或查询中区分对象、数组和基础标量

处理一列 JSON 文档时，最稳妥的分类入口是 JSON_TYPE()：它直接返回 OBJECT、ARRAY、INTEGER、STRING 等类型名。查询场景用 CASE 做业务归并；写入场景则放进 BEFORE 触发器，把分类结果保存到普通列。这样既保留原始 JSON，又让后续过滤和统计更简单。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-attribute-functions.html

先把整份文档和路径值分开判断

JSON_TYPE() 不负责遍历文档，它只回答“这个参数当前是什么 JSON 类型”。因此整列判断和路径判断应拆开写：前者看根节点，后者先取出路径值。下面的示例把一个对象、数组、整数和布尔值放在同一份文档中，展示返回标签。

-- 先判断根对象，再判断路径上的具体 JSON 值
SET @doc = '{"profile":{"name":"Lin","roles":["reader","admin"]},"age":28,"enabled":true}';

SELECT
  JSON_TYPE(@doc) AS root_type,
  JSON_TYPE(JSON_EXTRACT(@doc, '$.profile.roles')) AS roles_type,
  JSON_TYPE(JSON_EXTRACT(@doc, '$.age')) AS age_type,
  JSON_TYPE(JSON_EXTRACT(@doc, '$.enabled')) AS enabled_type;
-- 预期类型标签依次是 OBJECT、ARRAY、INTEGER、BOOLEAN

官方手册列出的返回值不只有三类：数字还可能是 DOUBLE、DECIMAL，字符串、二进制和日期时间也有各自标签。若业务只关心对象、数组、其他值，不要把这些具体标签硬编码成完整枚举。

用 CASE 归并成查询可用的业务标签

报表通常不需要区分 INTEGER 和 DECIMAL。可以先把路径值存到派生表达式，再用白名单归并；未知标签落到 scalar，避免以后 MySQL 增加类型名时查询直接漏行。

-- 把 payload.items 的类型归并为 object、array、scalar
SELECT id,
       CASE JSON_TYPE(JSON_EXTRACT(payload, '$.items'))
         WHEN 'OBJECT' THEN 'object'
         WHEN 'ARRAY' THEN 'array'
         WHEN 'NULL' THEN 'json-null'
         WHEN 'BOOLEAN' THEN 'scalar'
         ELSE 'scalar'
       END AS items_kind
FROM event_log
WHERE JSON_VALID(payload) = 1;
-- 先过滤无效文本；CASE 的 ELSE 负责其他合法标量类型

这里的边界很重要：JSON_VALID() 只返回输入是否是有效 JSON，不能替代类型判断。对声明为 JSON 的列，数据本身已经经过 JSON 语义约束；对 TEXT 列，则应先过滤或拒绝无效值，再调用 JSON_TYPE()。

在 BEFORE 触发器中保存类型分类

如果每次查询都重复解析同一个字段，可以增加一个分类列，例如 payload_kind，在写入前填好。MySQL 触发器按行触发，BEFORE 触发器可以通过 SET NEW.column 修改即将写入的列。

-- payload 使用 JSON 类型，payload_kind 使用 VARCHAR(16)
DELIMITER //
CREATE TRIGGER bi_event_log_payload_kind
BEFORE INSERT ON event_log
FOR EACH ROW
BEGIN
  -- 根节点分类：对象、数组之外统一归入 scalar
  SET NEW.payload_kind = CASE JSON_TYPE(NEW.payload)
    WHEN 'OBJECT' THEN 'object'
    WHEN 'ARRAY' THEN 'array'
    ELSE 'scalar'
  END;
END//
DELIMITER ;
-- UPDATE 场景还需另建 BEFORE UPDATE 触发器同步修改结果

如果分类只服务查询，也可以不落库，直接使用生成列或查询表达式；触发器适合写入路径固定、分类经常被筛选的表。若原始列允许 SQL NULL，触发器还要先决定 NULL 是否单独落为 missing，不能让它悄悄落入 scalar。

三个容易混淆的边界要先定下来

尤其不要把“路径不存在”和“路径值是 JSON null”混成一个结论。本文只讨论有效 JSON 值的类型分类；如果业务还要判断路径是否存在，应另行设计路径存在性检查和字段约定。

常见问题

JSON_TYPE 可以直接判断普通 VARCHAR 吗？

不能把任意普通字符串当作 JSON 值传入。字符串本身若要作为 JSON 字符串，需要包含合法 JSON 引号；不确定时先用 JSON_VALID() 做输入分流。

为什么数组里的数字返回 INTEGER 而不是 scalar？

这是 JSON_TYPE 的细粒度返回值。业务层可以在 CASE 中把 INTEGER、DOUBLE、DECIMAL 等统一映射为 scalar。

只建 BEFORE INSERT 触发器够吗？

如果 payload 会更新，就不够。应同步建立 BEFORE UPDATE 触发器，或改用生成列，让分类始终由当前 payload 派生。

落地时先决定分类列是否需要持久化，再决定保留细粒度标签还是统一成三类。对查询临时判断，用 JSON_TYPE(JSON_EXTRACT(...)) 足够；对高频筛选，写入时维护分类列更容易控制查询成本。