sql—如何将成对值的jsonb数组转换为行和列?

jmp7cifd  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(397)

考虑到我有一个 jsonb 具有成对值数组的列:

[1001, 1, 1002, 2, 1003, 3]

我想将每一对转换成一行,每一对的值作为列:

| a    | b |
|------|---|
| 1001 | 1 |
| 1002 | 2 |
| 1003 | 3 |

这样的事情有可能有效吗?
我发现了一些低效(慢)的方法,比如使用 LEAD() ,或使用下一行的值联接同一个表,但查询需要大约10分钟。
ddl地址:

CREATE TABLE products (
  id int not null,
  data jsonb not null
);

INSERT INTO products VALUES (1, '[1001, 1, 10002, 2, 1003, 3]')

分贝小提琴:https://www.db-fiddle.com/f/2qnnkmbqxf2fb9xjdj55sz/0
谢谢!

q5lcpyga

q5lcpyga1#

从声明的Angular 来看,这不是一种优雅的方法,但是您能看看这是否对您更有利吗?

with indexes as (
  select id, generate_series(1, jsonb_array_length(data) / 2) - 1 as idx
    from products
)
select p.id, p.data->>(2 * i.idx) as a, p.data->>(2 * i.idx + 1) as b
  from indexes i
  join products p on p.id = i.id;
laximzn5

laximzn52#

此查询

SELECT j.data
  FROM products
 CROSS JOIN jsonb_array_elements(data) j(data)

如果您只需要像演示中那样取消填充查询中的所有元素,那么应该运行得更快。
演示
或者甚至把柱子从 products 表格:

SELECT jsonb_array_elements(data)
  FROM products

或者你需要像这样回来

| a    | b |
|------|---|
| 1001 | 1 |
| 1002 | 2 |
| 1003 | 3 |

取消激活两列,然后使用:

SELECT MAX(CASE WHEN mod(rn,2) = 1 THEN data->>(rn-1)::int END) AS a,
       MAX(CASE WHEN mod(rn,2) = 0 THEN data->>(rn-1)::int END) AS b
  FROM
  (
   SELECT p.data, row_number() over () as rn
     FROM products p
    CROSS JOIN jsonb_array_elements(data) j(data)) q
  GROUP BY ceil(rn/2::float) 
  ORDER BY ceil(rn/2::float)

演示

相关问题