如何将嵌套JSON转换为PostgreSQL结构化数据表？

问题背景 #

我有如下嵌套JSON数据，需要将其键值对整理为指定格式的PostgreSQL数据表：

my_json = '[
    {
        "home": "team1",
        "away": "team2",
        "odds": {
            "periods": {
                "1": {
                    "scoring_types": {
                        "1": {
                            "bet_types": {
                                "1": {
                                    "outcomes": {
                                        "1": 1.85,
                                        "3": 3.85,
                                        "2": 6.50
                                    },
                                    "param": null
                                },
                                "2": {
                                    "outcomes": {
                                        "1": 1.9,
                                        "2": 1.9
                                    },
                                    "param": 8.5
                                }
                            }
                        }
                    }
                }
            }
        }
    }
]'

需求说明：为outcomes下的每个数值生成一行数据，保留对应的父级键值；periods、scoring_types等字段的数量可能动态变化。

目标数据表结构 #

现有查询（存在嵌套访问问题） #

我已经写出了能提取前三列的查询，但在访问深层嵌套键值时遇到困难：

SELECT
  home,
  away,
  (st).key AS scoring_type_id
FROM (
  SELECT
    home,
    away,
    json_each_text(odds->'scoring_types') AS st
  FROM
    json_to_recordset(my_json) AS data(home TEXT, away TEXT, odds JSON)
);

解决方案：逐层展开嵌套JSON #

要完成需求，需要逐层遍历嵌套的JSON对象，每一层用json_each或json_each_text展开，同时保留所有父级上下文信息。完整查询如下：

SELECT
  home,
  away,
  (st).key AS scoring_type_id,
  (p).key AS periods_id,
  (bt).key AS bet_types,
  (o).key AS outcomes,
  (bt_val)->>'param' AS param,
  (o).value::numeric AS value
FROM (
  SELECT
    home,
    away,
    (p).key,
    (p).value->'scoring_types' AS scoring_types
  FROM (
    SELECT
      home,
      away,
      json_each(odds->'periods') AS p
    FROM json_to_recordset(my_json) AS data(home TEXT, away TEXT, odds JSON)
  ) AS periods_data
) AS scoring_types_data,
json_each_text(scoring_types) AS st,
json_each((st).value->'bet_types') AS bt,
json_each_text((bt).value->'outcomes') AS o,
LATERAL (SELECT (bt).value AS bt_val) AS bet_type_details;

关键步骤说明： #

• 最外层解析：用json_to_recordset解析顶层JSON数组，提取home、away和odds核心字段。
• 遍历periods：通过json_each展开odds->'periods'，获取periods_id和对应子对象。
• 遍历scoring_types：基于上一步结果，用json_each_text展开scoring_types，获取scoring_type_id。
• 遍历bet_types：用json_each展开每个scoring_type下的bet_types，同时保留完整的bet_type对象用于提取param。
• 遍历outcomes：用json_each_text展开每个bet_type下的outcomes，拆分出outcomes标识和对应的数值value。
• 提取param：从bet_type对象中直接提取param字段，自动适配null值场景。

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