CODEWITHSUNDEEP
jsonpostgresql
juliomalegria
juliomalegria

Reputation: 24921

Select from JSON array in Postgres

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.

Upvotes: 1

Views: 530

Answers (2)

Bruno Calza
Bruno Calza

Reputation: 2780

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

Upvotes: 3

juliomalegria
juliomalegria

Reputation: 24921

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)

Upvotes: 2

Related Questions