How to convert JSONB array of pair values to rows and columns?

Question

Given that I have a jsonb column with an array of pair values:

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

I want to turn each pair into a row, with each pair values as columns:

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

Is something like that even possible in an efficient way?

I found a few inefficient (slow) ways, like using LEAD(), or joining the same table with the value from next row, but queries take ~ 10 minutes.

DDL:

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

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

DB Fiddle: https://www.db-fiddle.com/f/2QnNKmBqxF2FB9XJdJ55SZ/0

Thanks!

@AkhileshMishra what do you mean by that? They are "soft" paired, in a business requirements sense. They aren't paired in a software engineering sense. E.g. it is not a 2 dimensional array. — moltar
– moltar, Commented Jun 27, 2020 at 1:38

Mike Organek · Accepted Answer · 2020-06-25 17:24:20Z

1

This is not an elegant approach from a declarative standpoint, but can you please see whether this performs better for you?

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;

answered Jun 25, 2020 at 17:24

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

moltar Over a year ago

This indeed made our queries much faster!A select with 1000 limit took just 1.4 seconds. Previously the query ran for 100s. Thank you!

Barbaros Özhan · Accepted Answer · 2020-06-25 17:42:27Z

0

This query

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

should run faster if you just need to unpivot all elements within the query as in the demo.

Demo

or even remove the columns coming from products table :

SELECT jsonb_array_elements(data)
  FROM products

OR If you need to return like this

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

as unpivoting two columns, then use :

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)

Demo

edited Jun 25, 2020 at 17:42

answered Jun 25, 2020 at 15:38

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

1 Comment

moltar Over a year ago

Thank you, Barbaros for your answer. I liked that it was educational in nature. It was also an elegant solution. Unfortunately the resulting query is slower than our original query. It ran for ~ 7 minutes and didn't finish, so I killed it.

Collectives™ on Stack Overflow

How to convert JSONB array of pair values to rows and columns?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related