QuestDB中JSON数组格式数据的Unpivot/展开转换实现方案咨询

阿华AIGC实验室

2026-4-2

确实，目前QuestDB还没有正式推出UNPIVOT功能（官方已经在 roadmap 里了，期待后续更新！），不过针对你这种把预测数据存在JSON数组里、需要转成长格式的需求，有两个比较实用的临时替代方案，我之前处理类似场景的时候用过，分享给你：

方案一：利用`unnest`展开原生数组（推荐，适合可变长度数组）

QuestDB支持将JSON字符串转成原生数组，再通过unnest函数将数组展开为多行，而且可以同时展开多个长度相同的数组，自动配对对应位置的元素，完美匹配你需要的“每个数组位置对应一行”的需求。

具体SQL示例

假设你的表名为forecast_data，字段分别是timestamp（原始预测生成时间）、forecast_timestamps（存JSON数组的字符串，比如"[2,3]"）、forecasts（存预测值JSON数组的字符串，比如"[3,4]"）：

WITH expanded_forecasts AS (
  -- 先将JSON数组转成QuestDB原生数组，再展开
  SELECT
    unnest(JSON_TO_ARRAY(fd.forecast_timestamps)) AS timestamp,
    unnest(JSON_TO_ARRAY(fd.forecasts)) AS forecast,
    -- 计算每个预测在数组中的位置（从1开始计数）
    row_number() OVER (PARTITION BY fd.timestamp ORDER BY unnest(JSON_TO_ARRAY(fd.forecast_timestamps))) AS position
  FROM forecast_data fd
),
all_timestamps AS (
  -- 收集所有需要出现在结果中的时间戳：原始生成时间+所有预测时间
  SELECT timestamp FROM forecast_data
  UNION
  SELECT unnest(JSON_TO_ARRAY(forecast_timestamps)) FROM forecast_data
)
-- 左连接补全无预测的时间戳（比如你的示例中timestamp=1的NULL行）
SELECT
  at.timestamp,
  ef.forecast,
  ef.position
FROM all_timestamps at
LEFT JOIN expanded_forecasts ef ON at.timestamp = ef.timestamp
ORDER BY at.timestamp;

注意事项

这个方法要求每一行的forecast_timestamps和forecasts数组长度必须一致（你的场景里刚好满足），否则unnest会报错；
如果JSON数组的格式有问题（比如语法错误），JSON_TO_ARRAY会返回错误，建议提前用JSON_VALID校验数据；
性能表现很好，因为只需要扫描一次原始表，展开操作是内存级别的，适合大数据量场景。

方案二：用`UNION ALL`手动展开固定/有限长度数组

如果你的数组长度变化范围不大（比如最多5个元素），可以通过手动写UNION ALL分支来提取每个索引位置的元素，适合对SQL兼容性要求更高或者不想用数组函数的场景。

具体SQL示例

WITH raw_data AS (
  SELECT
    timestamp AS source_ts,
    forecast_timestamps,
    forecasts,
    JSON_LENGTH(forecast_timestamps) AS arr_len
  FROM forecast_data
),
-- 手动提取每个索引位置的元素，每个分支对应一个数组位置
unpivoted AS (
  -- 提取第1个元素（数组索引从0开始）
  SELECT
    JSON_EXTRACT_SCALAR(forecast_timestamps, '$[0]') AS timestamp,
    JSON_EXTRACT_SCALAR(forecasts, '$[0]') AS forecast,
    1 AS position
  FROM raw_data
  WHERE arr_len >= 1

  UNION ALL

  -- 提取第2个元素
  SELECT
    JSON_EXTRACT_SCALAR(forecast_timestamps, '$[1]') AS timestamp,
    JSON_EXTRACT_SCALAR(forecasts, '$[1]') AS forecast,
    2 AS position
  FROM raw_data
  WHERE arr_len >= 2

  -- 可以继续添加更多分支，覆盖你可能遇到的最大数组长度
  UNION ALL

  -- 补全无预测的原始时间戳行
  SELECT
    source_ts AS timestamp,
    NULL AS forecast,
    NULL AS position
  FROM raw_data
  WHERE NOT EXISTS (
    SELECT 1 FROM raw_data rd
    WHERE JSON_CONTAINS(rd.forecast_timestamps, CAST(raw_data.source_ts AS JSON))
  )
)
SELECT * FROM unpivoted ORDER BY timestamp;