MySQL JSON_TABLE 展开数组时如何保留缺失字段

MySQL 用 JSON_TABLE() 展开数组时，数组元素缺少某个可选字段，不应该让整条元素消失。做法是让行源使用 '$[*]'，再给可选列写上 NULL ON EMPTY；如果还要区分“没有这个键”和“键存在但值是 JSON null”，再增加一列 EXISTS PATH。

保留缺失字段的关键不是给 JSON 补键，而是把“数组元素生成行”和“列路径取值”分开处理：行源决定保留哪一行，NULL ON EMPTY 决定缺失列填什么，EXISTS PATH 决定字段是否真的出现过。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html

先把数组元素和字段取值分成两层

JSON_TABLE() 的第一个路径是行源。对一个对象数组使用 '$[*]'，结果的基本单位就是数组元素。COLUMNS 中的路径只负责给这一行投影列，所以对象里少了 city，影响的是 city 这一列，不是已经由行源建立的整行。

下面的例子故意让第二个对象没有 city。id 和 name 是必填字段，city 是可选字段：

WITH source AS (
    SELECT JSON_ARRAY(
        JSON_OBJECT('id', 101, 'name', '上海仓', 'city', '上海'),
        JSON_OBJECT('id', 102, 'name', '杭州仓')
    ) AS doc
)
SELECT jt.id, jt.name, jt.city, jt.city_present
FROM source
CROSS JOIN JSON_TABLE(
    source.doc,
    '$[*]' COLUMNS (
        id INT PATH '$.id' ERROR ON EMPTY, -- 主键缺失时拒绝静默导入。
        name VARCHAR(40) PATH '$.name' ERROR ON EMPTY, -- 名称是必填列。
        city VARCHAR(40) PATH '$.city' NULL ON EMPTY, -- 缺失城市只填 SQL NULL。
        city_present TINYINT EXISTS PATH '$.city' -- 单独记录 city 键是否存在。
    )
) AS jt;

结果中仍然有两行：第一行的 city 是“上海”，第二行的 city 是 SQL NULL，但第二行的 city_present 是 0。这正是“保留数组元素、允许可选列为空”的效果。

用 NULL ON EMPTY 保留数组元素对应的行

对可选标量列，明确写 NULL ON EMPTY 比依赖默认行为更容易读懂，也方便以后审查导入规则。它处理的是 JSON 路径没有匹配值的情况；它不会把空字符串改成 NULL，也不会把一个存在但类型不对的对象自动变成业务上可接受的值。

如果下游只关心显示值，单独的 city 列已经够用；如果要做补值、审计或数据质量统计，建议保留 city_present。否则“接口没传城市”和“接口传了 null”会在落库后变成同一种状态。

用 EXISTS PATH 区分缺失、JSON null 和空字符串

EXISTS PATH 列不负责返回城市文本，而是判断路径上是否有数据。把它和普通的 PATH 列并列声明，就能让查询结果携带状态信息。MySQL 文档对 JSON_TABLE() 的列类型也把两者分开：普通路径列负责取值，EXISTS PATH 负责返回 1 或 0。

SELECT jt.order_id,
       jt.city,
       jt.city_present,
       CASE
           WHEN jt.city_present = 0 THEN '缺少 city 键' -- 先判断键是否出现。
           WHEN jt.city IS NULL THEN 'city 明确为 JSON null' -- 键在，但值为空。
           WHEN jt.city = '' THEN 'city 是空字符串' -- 空文本不等于缺失。
           ELSE 'city 有实际文本'
       END AS city_state
FROM JSON_TABLE(
    '[
       {"order_id": 1, "city": "上海"},
       {"order_id": 2, "city": null},
       {"order_id": 3},
       {"order_id": 4, "city": ""}
     ]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.order_id' ERROR ON EMPTY, -- 订单号缺失时让导入失败。
        city VARCHAR(40) PATH '$.city' NULL ON EMPTY, -- 缺失和 JSON null 的值都可落为 NULL。
        city_present TINYINT EXISTS PATH '$.city' -- 用 0/1 补足存在性信息。
    )
) AS jt;

这里不要用 COALESCE(city, '未提供') 来代替存在性列，因为它会把 JSON null 与字段缺失一起覆盖成同一个显示文本。先保留原始状态，再在展示层或业务层决定是否补默认值，通常更稳妥。

必填字段、类型错误和嵌套数组要单独定规则

缺失字段只是一个输入状态，不能和类型转换错误混为一谈。订单号、业务编码这类必填列可以使用 ERROR ON EMPTY；可选金额则可以把缺失和类型错误分开写清楚：

COLUMNS (
    order_id INT PATH '$.order_id' ERROR ON EMPTY ERROR ON ERROR, -- 必填且必须是整数。
    amount DECIMAL(12, 2) PATH '$.amount'
        NULL ON EMPTY ERROR ON ERROR -- 缺失可为空，传入对象或非法数字则报错。
)

如果缺失的是嵌套数组，使用 NESTED PATH '$.items[*]' 时要观察父行和子列的关系。没有子项时，嵌套列可能以补空行的形式出现；若业务只要真正存在的子项，再用子项主键做过滤。不要为了消除空值而直接把整个父对象过滤掉，否则会丢失“父对象存在但暂时没有子项”的信息。

常见问题

NULL ON EMPTY 会不会删除缺少字段的数组元素？

不会。数组元素是否生成行由行源路径决定，NULL ON EMPTY 只决定当前列在路径无匹配时的值。

字段缺失和 JSON null 为什么都显示为 NULL？

普通 PATH 列的值投影确实可能相同，所以需要并列增加 EXISTS PATH 列，用 0/1 表示键是否出现。

可以用 DEFAULT ON EMPTY 直接补默认城市吗？

可以，但默认值属于 JSON 字符串并要符合目标列类型。若还需要审计原始输入，建议先保留 NULL 和存在性标记，再由业务层决定默认值。