MySQL JSON_TABLE 结合 JSON_KEYS 读取动态对象键名

当 JSON 对象的键名不是预先固定的，例如监控标签、设备属性或租户自定义字段，直接给 JSON_TABLE() 写死列名就会失去通用性。更稳妥的做法是先用 JSON_KEYS() 取得一层动态键名，再用 JSON_TABLE() 把键数组展开成关系行，最后用 JSON_EXTRACT() 回到原对象取值。这样得到的是“键名 + JSON 值”两列，既能查询，也不会误把不同类型的数据提前转成字符串。

本文使用 MySQL 8.4 的 JSON 函数语义。官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html

先把动态对象变成键名行

假设一条设备记录的 attrs 为 {"cpu":72,"memory":4096,"healthy":true}。第一步不是猜列名，而是读取对象的一层键：

-- 先取设备属性对象的一层键名；结果是 JSON 数组
SET @attrs = '{"cpu":72,"memory":4096,"healthy":true}';

SELECT JSON_KEYS(@attrs) AS key_list;
-- 预期得到 ["cpu", "memory", "healthy"]

JSON_KEYS() 对对象返回键数组；目标路径不存在、输入是 SQL NULL 或输入不是对象时，结果可能是 NULL。它不会自动深入 cpu 对象内部，所以“读取一层动态键”要和“递归遍历 JSON”分开处理。

用 JSON_TABLE 展开 key/value 关系

把 JSON_KEYS() 的结果当作 JSON_TABLE() 的输入，路径 '$[*]' 表示数组中的每个键各生成一行。值先保留为 JSON，再根据业务需要转型：

-- 每个动态键占一行，先保留原始 JSON 值，避免丢失类型
SELECT
    jt.key_name,
    JSON_EXTRACT(@attrs, CONCAT('$.', jt.key_name)) AS json_value
FROM JSON_TABLE(
    JSON_KEYS(@attrs),
    '$[*]' COLUMNS (
        -- PATH '$' 读取数组当前元素，也就是一个键名
        key_name VARCHAR(100) PATH '$'
    )
) AS jt;

结果可以是 cpu / 72、memory / 4096 和 healthy / true。这里的 json_value 仍是 JSON 表达式：数字、布尔值和对象的边界还在。若只需要文本，再对标量值使用 JSON_UNQUOTE()；不要一开始就把所有值定义成 VARCHAR。

从键名取值时要处理路径边界

用 CONCAT('$.', key_name) 拼路径适合键名只包含字母、数字和下划线的场景。键名包含点号、引号或括号时，不能盲拼路径，应改用更严格的 JSON 路径转义策略，或把键名和值在应用层处理。动态 SQL 也不要直接拼接未经约束的用户输入。

-- 用派生表复用键名；只演示稳定键名的读取方式
WITH key_rows AS (
    SELECT jt.key_name
    FROM JSON_TABLE(
        JSON_KEYS(@attrs),
        '$[*]' COLUMNS (
            -- 限制长度，避免异常文档制造过大的路径字符串
            key_name VARCHAR(100) PATH '$'
        )
    ) AS jt
)
SELECT
    key_name,
    JSON_UNQUOTE(JSON_EXTRACT(@attrs, CONCAT('$.', key_name))) AS text_value
FROM key_rows
WHERE key_name IN ('cpu', 'memory');

这个过滤条件是边界控制，不是为了掩盖动态键。若业务只允许一组白名单键，优先在 SQL 中筛选；若键集合完全开放，则应记录键数量、文档大小和异常键名，避免每次查询都解析不可控的 JSON。

空对象、JSON null 与嵌套值不要混为一谈

空对象的 JSON_KEYS() 结果是空数组，交给 JSON_TABLE() 后通常没有键行；缺失路径或 SQL NULL 则可能得到 SQL NULL。对象中存在键但值为 JSON null，又是“键存在、值为空”的第三种状态。若报表要区分这三类情况，应额外保存存在性和原始 JSON 值，而不是只看显示出来的字符串。

JSON_TABLE 能直接把未知键变成动态列吗？

不能。它需要在 COLUMNS 中声明输出列；未知键应先展开成行，再由查询或应用层决定如何聚合成列。

为什么 JSON_KEYS 取不到嵌套对象里的键？

它默认只读取选定对象的一层键。可以把嵌套路径作为第二个参数读取某一层，但不会替你递归遍历整棵 JSON 树。

动态键查询适合直接放在线接口吗？

只有文档小、键数量可控且访问频率低时才适合。稳定筛选条件应落到生成列或关系表，动态解析更适合分析、导入和清洗任务。

记住这条组合关系即可：JSON_KEYS() 负责发现键，JSON_TABLE() 负责把数组变成行，JSON_EXTRACT() 负责按键取值。先保留 JSON 类型，再根据字段白名单和访问频率决定是否转成文本或迁移到关系模型。