PostgreSQL跨表查询问题：匹配跟踪计划的最新观测数据

前提说明 #

假设两张表的JSONB数组匹配逻辑为：tracking_plan.parameters数组中每个对象包含param_id字段，observations.component数组中每个对象包含comp_param_id字段，匹配条件为两者值相等。如果你的实际匹配字段不同，替换对应字段名即可。同时默认观测记录表包含created_at字段用于判断最新数据（若你的表用其他字段排序，替换排序字段即可）。

示例表结构 #

-- 跟踪计划表
CREATE TABLE tracking_plan (
    id INT PRIMARY KEY,
    parameters JSONB,
    subject TEXT
);

-- 观测记录表
CREATE TABLE observations (
    id INT PRIMARY KEY,
    component JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心查询实现 #

步骤1：拆解数组并匹配最新观测 #

先将JSONB数组拆分为单行记录，再筛选每个参数的最新观测，最后关联到对应的跟踪计划：

WITH expanded_plans AS (
    SELECT 
        tp.id AS plan_id,
        tp.subject,
        param->>'param_id' AS param_id
    FROM tracking_plan tp,
         jsonb_array_elements(tp.parameters) param
),
ranked_observations AS (
    SELECT 
        comp->>'comp_param_id' AS comp_param_id,
        comp AS component_data,
        o.created_at,
        -- 按参数分组，取最新的观测记录
        ROW_NUMBER() OVER (PARTITION BY comp->>'comp_param_id' ORDER BY o.created_at DESC) AS rn
    FROM observations o,
         jsonb_array_elements(o.component) comp
)
SELECT 
    ep.plan_id,
    ep.subject,
    ep.param_id,
    ro.component_data,
    ro.created_at
FROM expanded_plans ep
LEFT JOIN ranked_observations ro 
    ON ep.param_id = ro.comp_param_id AND ro.rn = 1
ORDER BY ep.plan_id, ep.param_id;

步骤2：按跟踪计划聚合结果（可选） #

如果需要每个跟踪计划一行，聚合该计划下所有参数的最新观测数据：

WITH expanded_plans AS (
    SELECT 
        tp.id AS plan_id,
        tp.subject,
        param->>'param_id' AS param_id
    FROM tracking_plan tp,
         jsonb_array_elements(tp.parameters) param
),
ranked_observations AS (
    SELECT 
        comp->>'comp_param_id' AS comp_param_id,
        comp AS component_data,
        o.created_at,
        ROW_NUMBER() OVER (PARTITION BY comp->>'comp_param_id' ORDER BY o.created_at DESC) AS rn
    FROM observations o,
         jsonb_array_elements(o.component) comp
),
plan_aggregated AS (
    SELECT 
        ep.plan_id,
        ep.subject,
        jsonb_agg(
            CASE 
                WHEN ro.component_data IS NOT NULL 
                THEN jsonb_build_object(
                    'param_id', ep.param_id,
                    'latest_observation', ro.component_data,
                    'observed_at', ro.created_at
                )
                ELSE jsonb_build_object(
                    'param_id', ep.param_id,
                    'latest_observation', 'null'
                )
            END
        ) AS param_observations
    FROM expanded_plans ep
    LEFT JOIN ranked_observations ro 
        ON ep.param_id = ro.comp_param_id AND ro.rn = 1
    GROUP BY ep.plan_id, ep.subject
)
SELECT * FROM plan_aggregated;

关键要点 #

1. JSONB数组拆解：用jsonb_array_elements将数组拆分为单行记录，这是实现数组元素关联的基础。
2. 最新数据筛选：通过ROW_NUMBER()窗口函数按参数分组，按时间降序排名，仅保留排名为1的最新观测。
3. 全量计划返回：使用LEFT JOIN确保所有跟踪计划都能出现在结果中，无匹配观测的参数会显示null。
4. 灵活适配：若你的匹配字段、排序字段与示例不同，直接替换对应字段表达式即可。

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