Select from JSON array in Postgres

Question

Is there a way to do this in Postgres?

SELECT * FROM magic_json_function('[{"col1": 1, "col2": "A"}, {"col1": 2, "col2": "B"}]')

 col1 | col2 
------+------
 1    | A
 2    | B
(2 rows)

Edit: Without having to create a table.

Bruno Calza · Accepted Answer · 2014-11-05 22:31:31Z

3

Sure, the function is json_populate_recordset. Assuming there's a table test defined by

CREATE TABLE test(
  col1 int,
  col2 text
);

You can simply:

SELECT * FROM json_populate_recordset(NULL::test,'[{"col1": 1, "col2": "A"}, {"col1": 2, "col2": "B"}]')

answered Nov 5, 2014 at 22:31

Bruno Calza

2,8002 gold badges25 silver badges26 bronze badges

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

1 Comment

Jörn Horstmann Over a year ago

Instead of CREATE TABLE you can also use CREATE TYPE

juliomalegria · Accepted Answer · 2014-11-05 22:28:13Z

2

This is how I ended up doing it:

SELECT value->>'col1' AS col1, value->>'col2' AS col2
FROM json_array_elements('[{"col1": 1, "col2": "A"}, {"col1": 2, "col2": "B"}]')

 col1 | col2 
------+------
 1    | A
 2    | B
(2 rows)

answered Nov 5, 2014 at 22:28

juliomalegria

25k14 gold badges77 silver badges89 bronze badges

1 Comment

Bruno Calza Over a year ago

nice one. never would thought of it ;)

Collectives™ on Stack Overflow

Select from JSON array in Postgres

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