使用 JSONB 欄位從不同相關行收集值

假設我有以下表結構：

jobs
+----+------------+
| id | some_field |
+----+------------+
|  1 |  some_val  |
|  2 |  some_val  |
|  3 |  some_val  |
+----+------------+

events
+----+--------+----------------------------------------+
| id | job_id |            payload (JSONB)             |
+----+--------+----------------------------------------+
|  1 |      1 | {'type':1, 'a':'some_val', ...}        |
|  2 |      1 | {'type':2, 'c':'some_other_val1', ...} |
|  3 |      2 | {'type':1, 'a':'some_other_val2', ...} |
|  4 |      2 | {'type':1, 'a':'some_other_val3', ...} |
|  5 |      3 | {'type':1, 'a':'some_other_val4', ...} |
|  6 |      3 | {'type':1, 'a':'some_other_val5', ...} |
|  7 |      3 | {'type':2, 'c':'some_other_val6', ...} |
|  8 |      3 | {'type':3, 'd':'some_other_val7', ...} |
+----+--------+----------------------------------------+

我想創建一個查詢/視圖…

• …如果有一個事件，則為每個作業選擇一行payload->>'type' = 2
• … 在該行中，顯示與此作業關聯的其他事件的值。
• … 在該行中，如果該事件類型多次發生，則顯示與此作業關聯的其他事件的值。

例如，使用上述數據，我想選擇所有具有 type 事件的作業，2並從具有 type 的事件中找出該d欄位的值，並從具有 type的事件中找出該欄位的3一些聚合（例如COUNT()）。結果將是：a``1

Results
+--------+------------------+----------+
| job_id |        d         | COUNT(a) |
+--------+------------------+----------+
|      1 |  NULL            |        1 |
|      3 |  some_other_val7 |        2 |
+--------+------------------+----------+

作為獎勵：每個job事件只能有一個 type 事件2，因此基本查詢的結構可以如下：

SELECT
 job.id
FROM
 event
LEFT JOIN 
   job ON event.job_id = job.id
WHERE
 event.payload ->> 'type' = 2;

我怎樣才能得到結果表？我是否需要對每個欄位/事件類型進行子查詢，這會導致性能相對較差，或者我可以GROUP BY job_id在事件表上使用一些並加入 JSON 嗎？（請注意，我的數據更複雜，並且我想要選擇的有效負載中有更多欄位）。

---

這是一個帶有範例數據和我的測試查詢的 DB Fiddle：

CREATE TABLE job (
      id SERIAL PRIMARY KEY,
      some_field TEXT
);

CREATE TABLE event (
      id SERIAL PRIMARY KEY,
      job_id INTEGER,
      payload JSONB
);

INSERT INTO job (some_field) VALUES ('val1'), ('val2'), ('val3');
INSERT INTO event (job_id, payload) VALUES 
    (1, '{"type":1, "a":"some_val"}'::json),
    (1, '{"type":2, "c":"some_other_val1"}'::json),
    (2, '{"type":1, "a":"some_other_val2"}'::json),
    (2, '{"type":1, "a":"some_other_val3"}'::json),
    (3, '{"type":1, "a":"some_other_val4"}'::json),
    (3, '{"type":1, "a":"some_other_val5"}'::json),
    (3, '{"type":2, "c":"some_other_val6"}'::json),
    (3, '{"type":3, "d":"some_other_val7"}'::json);

SELECT job.id, job.some_field 
FROM event 
LEFT JOIN  job 
     ON event.job_id = job.id WHERE event.payload ->> 'type' = '2';

編號 | 一些欄位
-: | :---------
 1 | val1 
 3 | val3 

db<>在這裡擺弄

根據評論，您需要一個時間戳欄位，我已將其添加到您的範例數據中。現在恕我直言，您應該首先取消嵌套您的 jsonb 數據。為此，我使用了 SUM(CASE，它也計算了最小時間戳值。

SELECT 
   event.job_id, 
   job.some_field,
   event.payload->>'type' AS type, 
   SUM(CASE WHEN event.payload->>'a' IS NOT NULL THEN 1 ELSE 0 END) AS a,
   SUM(CASE WHEN event.payload->>'b' IS NOT NULL THEN 1 ELSE 0 END) AS b,
   SUM(CASE WHEN event.payload->>'c' IS NOT NULL THEN 1 ELSE 0 END) AS c,
   SUM(CASE WHEN event.payload->>'d' IS NOT NULL THEN 1 ELSE 0 END) AS d,
   MIN((event.payload->>'ts'::text)::timestamp) as min_ts
FROM   
   event
JOIN
   job
   ON event.job_id = job.id
GROUP BY 
   event.job_id, 
   job.some_field,
   event.payload->>'type'
ORDER BY
   1, 2;

這是結果：

工作ID | 一些欄位 | 類型 | 一個 | 乙 | c | d | min_ts 
-----: | :--------- | :--- | -: | -: | -: | -: | :------------------
 1 | val1 | 1 | 1 | 0 | 0 | 0 | 2004-10-19 10:23:54
 1 | val1 | 2 | 0 | 0 | 1 | 0 | 2002-10-19 10:23:54
 2 | val2 | 1 | 2 | 0 | 0 | 0 | 2003-10-19 10:23:54
 3 | val3 | 1 | 2 | 0 | 0 | 0 | 2004-10-19 10:23:54
 3 | val3 | 2 | 0 | 0 | 1 | 0 | 2005-10-19 10:23:54
 3 | val3 | 3 | 0 | 0 | 0 | 1 | 2006-10-19 10:23:54

db<>在這裡擺弄

從此時開始，您可以輕鬆地按類型過濾它並獲得所需的結果。

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