在單個查詢中從一對多關係數據建構 JSON 對象？

我有一個 PostgreSQL 9.5.3 數據庫，其中包含如下表：

container
   id: uuid (pk)
   ... other data

thing
   id: uuid (pk)
   ... other data

container_thing
   container_id: uuid (fk)
   thing_id: uuid (fk)
   primary key (container_id, thing_id)

Acontainer可以指向任意數量的things（沒有重複），並且 athing可以被任意數量的containers 指向。

可能有大量的容器和東西（取決於我們有多少客戶）。每個容器中可能只有 1 到 10 件東西。我們一次最多只能查詢大約 20 個容器。一個容器可以是空的，我需要取回一個空數組。

我需要建構代表容器的 json 對象，如下所示：

{
   "id": "d7e1bc6b-b659-432d-b346-29f3a530bfa9",
   ... other data
   "thingIds": [
       "4e3ad81b-f2b5-4220-8e0e-e9d53c80a214",
       "f26f49e5-76b4-4363-9ffe-9654ba0b0f0d"
   ]
}

這工作正常，但我通過使用兩個查詢來做到這一點：

select * from "container" where "id" in (<list of container ids>)
select * from "container_thing" where "container_id" in (<list of container ids>)

然後，我按程序為每個容器建構“thingIds”數組。

後來我找到了一個具有相關子查詢的解決方案，目前對我來說效果很好。

select *, array(select thing_id from container_thing where container_id = c.id) as "thingIds"
from container c;

有沒有更好的方法來做到這一點，也許以某種方式使用連接？

似乎它們總是生成一組行，這意味著container為每個thing指向的數據複製數據。

如果容器可以是空的，則目前接受的解決方案不適合您。它必須是一個外連接來保留沒有匹配的行 - 以獲得與您在小提琴中使用的相關子查詢等效的結果：

select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container

1.簡單查詢

SELECT to_json(sub.*) AS container_with_things
FROM  (
  SELECT c.*, json_agg(thing_id) AS "thingIds"
  FROM   container c
  **LEFT**   JOIN container_thing ct ON  ct.container_id = c.id
  WHERE  c.id IN (<list of container ids>)
  GROUP  BY c.id
  ) sub;

2.加入前聚合

每個容器的行數超過幾行（您提到了 20 行），在加入之前聚合通常更快：

SELECT to_json(sub.*) AS container_with_things
FROM  (
  SELECT c.*, ct."thingIds"
  FROM   container c
  LEFT   JOIN (
     SELECT container_id AS id, json_agg(thing_id) AS "thingIds"
     FROM   container_thing
     WHERE  container_id IN (<list of container ids>) -- repeat condition
     GROUP  BY 1
     ) ct USING (id)
  WHERE  c.id IN (<list of container ids>)
  ) sub;

3.LEFT JOIN LATERAL使用 ARRAY 建構子

將ARRAY 建構子與LEFT JOIN LATERAL：

SELECT to_json(sub.*) AS container_with_things
FROM  (
  SELECT c.*, ct."thingIds"
  FROM   container c
  LEFT   JOIN LATERAL (
     SELECT ARRAY (
        SELECT thing_id
        FROM   container_thing
        WHERE  container_id = c.id
        -- ORDER  BY thing_id  -- optional order for deterministic results
        ) AS "thingIds"
     ) ct ON true
  WHERE  c.id IN (<list of container ids>)
  ) sub;

可能會更快，但是。看：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

在上面的三個查詢中，空容器的結果略有不同：

1. "thingIds":[null]
2. "thingIds":null
3. "thingIds":[]

4.jsonb_set()在 Postgres 9.5 中

在 Postgres 9.5或更高版本中，您還可以使用jsonband jsonb_set()- 和少一個子查詢：

SELECT jsonb_set(to_jsonb(c.*), '{thingIds}', COALESCE(ct."thingIds", '[]')) AS container_with_things
FROM   container c
LEFT   JOIN (
  SELECT container_id AS id, jsonb_agg(thing_id) AS "thingIds"
  FROM   container_thing
  WHERE  container_id IN (<list of container ids>) -- repeat condition
  GROUP  BY 1
  ) ct USING (id)
WHERE  c.id IN (<list of container ids>);

或者：

SELECT to_jsonb(c) || jsonb_build_object('thingIds', "thingIds") AS container_with_things
FROM   ... 

無論哪種方式，container_with_things如果為 NULL，則整體設置為"thingIds"NULL，因此請投入COALESCE以防止這種情況。

5.jsonb_set_lax()在 Postgres 13 中

jsonb_set_lax()有辦法處理NULL：

SELECT jsonb_set_lax(to_jsonb(c.*), '{thingIds}', ct."thingIds") AS container_with_things
FROM   container c
LEFT   JOIN (
  SELECT ct.container_id AS id, jsonb_agg(ct.thing_id) AS "thingIds"
  FROM   container_thing ct
  WHERE  ct.container_id IN ('1','3','4')  -- repeat condition
  GROUP  BY 1
  ) ct USING (id)
WHERE  c.id IN ('1','3','4');

預設情況下，JSON NULL 用作值，但還有其他選項。閱讀手冊。

db<>fiddle here

舊sqlfiddle

引用自：https://dba.stackexchange.com/questions/144102