MySQL使用JSON_TABLE查询多JSON数组结果错位如何按索引对齐

多个并列的NESTED PATH子句会对每个数组做独立的笛卡尔展开，每个数组遍历生成的行互不关联，无匹配的字段自动填充NULL，最终产生错位和多余行，无法按数组索引自动对齐。

核心逻辑：只遍历任意一个数组生成从1开始的位置序号，其余数组根据这个序号按下标直接取值，不需要写多个NESTED PATH。

注意：MySQL原生JSON数组的下标从0开始，FOR ORDINALITY生成的序号从1开始，取值时需要做偏移计算。

兼容所有MySQL 8.0版本的写法 #

SELECT
  nid AS nid_1,
  Fault,
  nid AS nid_2,
  JSON_UNQUOTE(JSON_EXTRACT(
    src.json_doc, 
    CONCAT('$.resolutions[', nid - 1, ']')
  )) AS Resolution,
  nid AS nid_3,
  JSON_UNQUOTE(JSON_EXTRACT(
    src.json_doc, 
    CONCAT('$.uspn[', nid - 1, ']')
  )) AS USPN
FROM (
  -- 实际使用时替换为业务表+JSON字段即可，无需单独写子查询
  SELECT '{"0":"","faults":["Damaged Metal Tray","Failed Max Load","Damaged Hood Assembly","No Power \/ Dead Control Board"],"resolutions":["Replaced Metal Tray","Replaced CAL Cover","Replaced Load Cell Housing","Replaced Control Board"],"kg":["","7.509","",""],"uspn":["110432","","214421",""],"pcb_replaced":"1","notes":""}' AS json_doc
) src,
JSON_TABLE(
  src.json_doc,
  '$.faults[*]' COLUMNS (
    nid FOR ORDINALITY,
    Fault TEXT PATH '$'
  )
) j;

MySQL 8.0.21+ 简化写法 #

8.0.21及以上版本支持JSON路径中直接引用生成列，可以省略JSON_EXTRACT拼接路径的步骤：

SELECT
  nid_1,
  Fault,
  nid_1 AS nid_2,
  Resolution,
  nid_1 AS nid_3,
  USPN
FROM JSON_TABLE(
    '{"0":"","faults":["Damaged Metal Tray","Failed Max Load","Damaged Hood Assembly","No Power \/ Dead Control Board"],"resolutions":["Replaced Metal Tray","Replaced CAL Cover","Replaced Load Cell Housing","Replaced Control Board"],"kg":["","7.509","",""],"uspn":["110432","","214421",""],"pcb_replaced":"1","notes":""}',
    '$.faults[*]' COLUMNS (
        nid_1 FOR ORDINALITY,
        Fault TEXT PATH '$',
        Resolution TEXT PATH '$.resolutions[$(nid_1 - 1)]',
        USPN TEXT PATH '$.uspn[$(nid_1 - 1)]'
    )
) j;

上述两种写法执行后都会返回预期的对齐结果，无多余NULL行，三个数组同位置的元素会展示在同一行。
该方案仅适用于「多个数组元素数量完全一致」的场景，如果数组长度不匹配，短数组超出长度的位置会返回NULL。

内容的提问来源于stack exchange，提问作者Jiri