如何从含JSON列的表中提取嵌套值，生成指定结构数据表？

我有一张包含id字段和名为prod的JSON列的表，该JSON列的结构如下：

{
  "1": {
    "product": "chain",
    "spare_part": [],
    "sp_done": 0
  },
  "2": {
    "product": "bike",
    "spare_part": {
      "1": {"part": "pedal"},
      "2": {"part": "switch gear"}
    },
    "sp_done": 1
  }
}

需要将其转换为如下结构的数据表：

PostgreSQL 解决方案 #

利用PostgreSQL的JSONB处理函数展开嵌套结构，适配spare_part为数组或对象的场景：

SELECT
    t.id,
    j.product,
    CASE
        WHEN jsonb_typeof(j.spare_part) = 'object' THEN sp.part
        ELSE ''
    END AS spare_part,
    j.sp_done
FROM
    your_table t,
    -- 展开prod列的顶层键值对
    jsonb_each(t.prod::jsonb) AS j_obj(key, value),
    -- 提取JSON对象中的字段
    jsonb_to_record(j_obj.value) AS j(product text, spare_part jsonb, sp_done int),
    -- 处理spare_part：对象则展开，数组则生成空值行
    LATERAL (
        SELECT part
        FROM jsonb_each_text(j.spare_part) AS sp(key, part)
        UNION ALL
        SELECT ''
        WHERE jsonb_typeof(j.spare_part) = 'array'
    ) sp
ORDER BY
    t.id, spare_part;

MySQL 解决方案 #

基于MySQL 8.0+的JSON_TABLE函数实现结构转换：

WITH prod_expanded AS (
    SELECT
        t.id,
        j.*
    FROM
        your_table t,
        -- 展开顶层JSON对象
        JSON_TABLE(
            JSON_KEYS(t.prod),
            '$[*]' COLUMNS (
                key_val VARCHAR(20) PATH '$',
                product VARCHAR(50) PATH CONCAT('$.', '$', '.product'),
                spare_part JSON PATH CONCAT('$.', '$', '.spare_part'),
                sp_done INT PATH CONCAT('$.', '$', '.sp_done')
            )
        ) j
)
SELECT
    pe.id,
    pe.product,
    COALESCE(sp.part, '') AS spare_part,
    pe.sp_done
FROM
    prod_expanded pe
LEFT JOIN JSON_TABLE(
    -- 仅展开对象类型的spare_part
    CASE JSON_TYPE(pe.spare_part)
        WHEN 'OBJECT' THEN pe.spare_part
        ELSE '{}'
    END,
    '$.*' COLUMNS (part VARCHAR(50) PATH '$.part')
) sp ON TRUE
-- 为数组类型的spare_part补充空值行
UNION ALL
SELECT
    pe.id,
    pe.product,
    '',
    pe.sp_done
FROM
    prod_expanded pe
WHERE JSON_TYPE(pe.spare_part) = 'ARRAY'
ORDER BY id, spare_part;

注意事项 #

• 替换SQL中的your_table为实际表名
• 若需要将空字符串替换为NULL，直接把语句中的''改成NULL即可

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