Postgres在JSON列之間聚合

這里需要一些聚合級別。

• 首先，在相關的子查詢中，將filled_rows和item_ids分解為單獨的行。
• 按item_id聚合，取最小值row_id。
• 通過row_id進行聚合，并獲取item_ids的JSON數(shù)組。
• 將整件事聚合起來，在一個JSON中獲得所有行

SELECT
  tp.id,
  r.result
FROM template_post tp
CROSS JOIN LATERAL (
    SELECT
      jsonb_build_object(
        'filled_rows', jsonb_agg(byRow.*)
      ) AS result
    FROM (
        SELECT
          byItem.row_id,
          jsonb_agg(byItem.item_id) AS item_ids
        FROM (
            SELECT
              MIN((r.value ->> 'row_id') ::int) AS row_id,
              i.value::int AS item_id
            FROM response_post rp
            CROSS JOIN jsonb_array_elements(rp.data -> 'filled_rows') r
            CROSS JOIN jsonb_array_elements_text(r.value -> 'item_ids') i
            WHERE rp.template_id = tp.id
            GROUP BY
              i.value::int
        ) byItem
        GROUP BY
          byItem.row_id
    ) byRow
) r;

db<>fiddle