将表A动态JSON列键映射到表B匹配ID并重组时序数据

现有表A，data为JSON列，timestamp为带时区的时间戳列，结构及数据如下：

另有表B，结构及数据如下：

需求：将表A中动态生成的data列键（如a_123）与表B的id关联，生成以id为维度，聚合对应所有时间戳数据的目标表，结构如下：

步骤说明 #

1. 拆解JSON动态键：使用jsonb_each（若data为JSON类型则用json_each）将表A中data列的每个键值对拆分为独立行，关联对应的timestamp。
2. 按ID聚合时间序列数据：使用jsonb_object_agg将同一id对应的所有timestamp和其数据聚合为目标JSON结构。
3. 关联表B补充维度信息：将聚合后的结果与表B关联，补全location、elevation、tag字段。

完整SQL代码 #

SELECT
    b.id,
    b.location,
    b.elevation,
    b.tag,
    jsonb_object_agg(a.timestamp::text, a.data_value) AS data
FROM
    (
        SELECT
            timestamp,
            jsonb_each(data) AS (sensor_id, data_value)
        FROM
            A
    ) AS a
JOIN
    B ON a.sensor_id = b.id
GROUP BY
    b.id, b.location, b.elevation, b.tag;

代码解释 #

• jsonb_each(data)：将data列的JSON对象拆分为(key, value)行，这里key对应传感器ID（如a_123），value对应该时间点的监测数据。
• jsonb_object_agg(a.timestamp::text, a.data_value)：按id分组，将每个时间戳转为字符串作为键，对应的监测数据作为值，聚合为一个JSON对象。
• 关联表B：通过拆分出的sensor_id与表B的id匹配，补充维度字段。

• jsonb_each/json_each：用于将JSON对象拆分为键值对行的函数，可参考PostgreSQL官方文档中JSON函数章节的"JSON对象遍历函数"部分。
• jsonb_object_agg：用于将行数据聚合为JSON对象的聚合函数，对应官方文档中JSON聚合函数章节。

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