How to extract values from array json column into multiple rows in Postgresql?

Question

How can I extract values from the json arrays in ranges column as multiple rows Postgresq?

CREATE TABLE test_table (
  id INTEGER, 
  ranges jsonb
);

INSERT INTO test_table(id, ranges) VALUES
(1,'[{"End": 100, "Start": 1}, {"End": 1000, "Start": 101}]'),
(2,'[{"End": 2000, "Start": 1001}, {"End": 2002, "Start": 2001}]')
;

Expected result:

Start	End
1	100
101	1000
1001	2000
2001	2002

blackbishop · Accepted Answer · 2021-02-11 23:33:51Z

3

You can use jsonb_to_recordset function for this :

SELECT ranges."Start",
       ranges."End"
FROM   test_table,
       jsonb_to_recordset(test_table.ranges) AS ranges("End" int, "Start" int)

http://www.sqlfiddle.com/#!17/22d62/10

answered Feb 11, 2021 at 23:33

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

How to extract values from array json column into multiple rows in Postgresql?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related