如何使用JSON_TABLE转换MySQL表中的字典类型details列

AI 小北

2026-8-5

问题：MySQL解析非标准JSON格式列并转换为结构化表格

我有一个名为prop的MySQL表，其中details列存储的是多个JSON对象用逗号分隔的非标准格式数据，示例数据如下：

fnum    details
55      '{"a":"3"},{"b":"2"},{"d":"1"}'

我尝试用以下SQL将其转换为结构化表格，但未得到预期结果：

SELECT p.fnum, deets.*
FROM prop p
JOIN JSON_TABLE( p.details,
     '$[*]'
     COLUMNS (
              idx FOR ORDINALITY,
              a varChar(10) PATH '$.a',
              b varchar(20) PATH '$.b',
              d varchar(45) PATH '$.d'
              )
     ) deets

预期输出

当只有一行数据时：

fnum   a   b   d
55     3   2   1

当表中有两行数据时：

fnum     details
55      '{"a":"3"},{"b":"2"},{"d":"1"}'
56      '{"c":"car"}'

预期生成结果：

fnum    a       b      d       c
55      3       2      1       null
56      null    null   null    car

解决方案

问题核心在于details列的内容不是合法的JSON数组，而是多个JSON对象直接用逗号拼接的格式，无法被JSON_TABLE直接解析。需要先将其转换为合法的JSON数组，再解析后聚合合并行：

SELECT 
  p.fnum,
  MAX(deets.a) AS a,
  MAX(deets.b) AS b,
  MAX(deets.d) AS d,
  MAX(deets.c) AS c
FROM prop p
JOIN JSON_TABLE(
  -- 去除原始数据中的单引号，再包裹成JSON数组格式
  CONCAT('[', REPLACE(p.details, '''', ''), ']'),
  '$[*]' COLUMNS (
    a VARCHAR(10) PATH '$.a',
    b VARCHAR(20) PATH '$.b',
    d VARCHAR(45) PATH '$.d',
    c VARCHAR(45) PATH '$.c'
  )
) deets
GROUP BY p.fnum;

说明

格式修正：通过CONCAT('[', REPLACE(p.details, '''', ''), ']')把非标准格式转换为合法JSON数组，例如将'{"a":"3"},{"b":"2"},{"d":"1"}'转为[{"a":"3"},{"b":"2"},{"d":"1"}]。如果你的details列存储时本身没有外层单引号，可去掉REPLACE部分，直接用CONCAT('[', p.details, ']')。
解析与聚合：JSON_TABLE会将数组中的每个JSON对象拆分为单独行，再通过GROUP BY p.fnum和MAX()聚合函数，把同一fnum下的非null值合并到一行，得到预期的结构化结果。