主页 / dba / 问题 / 307724
Accepted
IBot
Asked: 2022-02-20 06:36:03 +0800 CST

为每个 jsonb 值对添加一个新行

  • 772

如果我正在运行查询，我有这张表：

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

目前的结果是：

分组日期 地点 数据
2018-06-01 00:00:00 位置_00 {“1”：1，“2”：空，“3”：空，“4”：1，“5”：8}
2018-05-01 00:00:00 位置_00 {“1”：空，“2”：9，“3”：10，“4”：8，“5”：3}

我想应用另一个 SELECT，为每个没有空值的值对添加一行，其中键进入 thing_type 列，值进入总列；像这样：

分组日期 地点 事物类型 全部的
2018-06-01 00:00:00 位置_00 1 1
2018-06-01 00:00:00 位置_00 4 1
2018-06-01 00:00:00 位置_00 5 8
2018-05-01 00:00:00 位置_00 2 9
2018-05-01 00:00:00 位置_00 3 10
2018-05-01 00:00:00 位置_00 4 8
2018-05-01 00:00:00 位置_00 5 3

小提琴数据库可以在这里找到。

更新：

感谢@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 postgresql-13

1 个回答

  1. Best Answer
    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;

    见分贝<>小提琴

    • 4

相关问题