如何按条件从嵌套数组提取数据生成扁平化CSV数据表（不建索引）

核心思路 #

由于模型数量有限可硬编码，且不能使用UNNEST展开输出，我们可以通过子查询+数组遍历的方式，针对每个硬编码的模型ID，精准提取daysAhead=1对应的passengers值，最终输出符合CSV导出要求的扁平化结构。

示例查询（BigQuery） #

假设你的数据表结构包含forecast_date（预测日期）和predictionModels（包含模型ID与预测数组的STRUCT数组），以下是可直接使用的查询语句：

WITH sample_data AS (
  -- 模拟生产环境的数据结构
  SELECT 
    DATE('2024-05-01') AS forecast_date,
    [
      STRUCT('model_001' AS modelID, 
             [STRUCT(1 AS daysAhead, 120 AS passengers), STRUCT(7 AS daysAhead, 180 AS passengers)] AS predictions),
      STRUCT('model_002' AS modelID, 
             [STRUCT(1 AS daysAhead, 150 AS passengers), STRUCT(7 AS daysAhead, 220 AS passengers)] AS predictions),
      STRUCT('model_003' AS modelID, 
             [STRUCT(1 AS daysAhead, 135 AS passengers), STRUCT(7 AS daysAhead, 190 AS passengers)] AS predictions)
    ] AS predictionModels
)
SELECT
  forecast_date,
  -- 对每个硬编码的模型ID，提取daysAhead=1的乘客数
  (SELECT p.passengers 
   FROM UNNEST(predictionModels) pm
   CROSS JOIN UNNEST(pm.predictions) p
   WHERE pm.modelID = 'model_001' AND p.daysAhead = 1) AS model_001,
  (SELECT p.passengers 
   FROM UNNEST(predictionModels) pm
   CROSS JOIN UNNEST(pm.predictions) p
   WHERE pm.modelID = 'model_002' AND p.daysAhead = 1) AS model_002,
  (SELECT p.passengers 
   FROM UNNEST(predictionModels) pm
   CROSS JOIN UNNEST(pm.predictions) p
   WHERE pm.modelID = 'model_003' AND p.daysAhead = 1) AS model_003
FROM sample_data

PostgreSQL 适配版本 #

如果使用PostgreSQL（JSONB类型存储模型数据），可使用以下查询：

WITH sample_data AS (
  SELECT 
    '2024-05-01'::DATE AS forecast_date,
    '[
      {"modelID": "model_001", "predictions": [{"daysAhead": 1, "passengers": 120}, {"daysAhead":7, "passengers":180}]},
      {"modelID": "model_002", "predictions": [{"daysAhead": 1, "passengers": 150}, {"daysAhead":7, "passengers":220}]},
      {"modelID": "model_003", "predictions": [{"daysAhead": 1, "passengers": 135}, {"daysAhead":7, "passengers":190}]}
    ]'::JSONB AS predictionModels
)
SELECT
  forecast_date,
  (SELECT (p->>'passengers')::INT
   FROM jsonb_array_elements(predictionModels) pm
   JOIN jsonb_array_elements(pm->'predictions') p ON true
   WHERE pm->>'modelID' = 'model_001' AND (p->>'daysAhead')::INT = 1) AS model_001,
  (SELECT (p->>'passengers')::INT
   FROM jsonb_array_elements(predictionModels) pm
   JOIN jsonb_array_elements(pm->'predictions') p ON true
   WHERE pm->>'modelID' = 'model_002' AND (p->>'daysAhead')::INT = 1) AS model_002,
  (SELECT (p->>'passengers')::INT
   FROM jsonb_array_elements(predictionModels) pm
   JOIN jsonb_array_elements(pm->'predictions') p ON true
   WHERE pm->>'modelID' = 'model_003' AND (p->>'daysAhead')::INT = 1) AS model_003
FROM sample_data

为什么之前的CASE语句可能失效 #

多数情况下，CASE语句失败是因为仅检查了数组的第一个元素，没有遍历整个predictions数组找到daysAhead=1的项，或者未正确关联modelID与对应的预测子集。上述子查询方式会遍历所有模型与预测项，确保精准匹配目标条件。

目标输出格式 #

执行查询后可直接导出为CSV，格式如下：

forecast_date,model_001,model_002,model_003
2024-05-01,120,150,135

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