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.

Answer 1

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"}]')

• Jörn Horstmann: Instead of CREATE TABLE you can also use CREATE TYPE

Answer 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)

• Bruno Calza: nice one. never would thought of it ;)