為每個 jsonb 值對添加一個新行

如果我正在執行查詢，我有這張表：

CREATE TABLE IF NOT EXISTS public.preprocess_things
(
   preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::regclass),
   arrive_date date NOT NULL,
   arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
   data jsonb NOT NULL,
   CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
   CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
)

我正在執行的查詢是這樣的：

SELECT DATE_TRUNC('month', arrive_date) AS grouped_date,
 LOWER(arrive_location) AS location,
 json_build_object(
   '1', SUM((data->'1')::int),
   '2', SUM((data->'2')::int),
   '3', SUM((data->'3')::int),
   '4', SUM((data->'4')::int),
   '5', SUM((data->'5')::int)
   ) AS data
FROM preprocess_things
GROUP BY grouped_date,
 location

目前的結果是：

我想應用另一個 SELECT，為每個沒有空值的值對添加一行，其中鍵進入 thing_type 列，值進入總列；像這樣：

小提琴數據庫可以在這裡找到。

更新：

感謝@Gerard H. Pille，我稍微調整了一下他對此的回應：

SELECT
 DATE_TRUNC('month', arrive_date) AS grouped_date,
 LOWER(arrive_location) AS location,
 x.key::int thing_type, sum(x.value::int) total
FROM preprocess_things
JOIN  jsonb_each(data) x ON (x.key::int = ANY('{1,2,3}'::int[]))
GROUP BY grouped_date,  location, x.key::int
ORDER BY grouped_date,  location, x.key::int;

由於在某些情況下，我只需要選擇 thing_type 而不是全部計算，唯一的問題是通過添加 JOIN 並比較查詢速度不是那麼好。

SELECT
 DATE_TRUNC('month', arrive_date) AS grouped_date,
 LOWER(arrive_location) AS location,
 x.key::int  thing_type, sum(x.value::int) total
   FROM preprocess_things,
        jsonb_each(data) x
 GROUP BY grouped_date,  location, x.key::int
 order by grouped_date,  location, x.key::int;

見分貝<>小提琴

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