Using json_populate_recordset without creating a table?

Question

I have a table in my db that has a column containing json records.

id | json_records
---+-------------
 0 | "[{'x1' : 1234, 'x2' : 5678},{'x1' : 2345, 'x2' : 6789}]'
 1 | "[{'x1' : 4321, 'x2' : 8765},{'x1' : 5432, 'x2' : 9876}]'

I would like to get something like this:

id |   x1 |   x2
---+------+-----
 0 | 1234 | 5678
 0 | 2345 | 6789
 1 | 4321 | 8765
 1 | 5432 | 9876

but I am having trouble getting the query to work:

select json_populate_recordset(json_records) from my_table

The few examples I've seen using json_populate_recordset insert the results into a table, but I am just trying to return the results. Is there a way to do this without creating a new table?

Answer 1

You have to create a new type to pass to the function (note that as the json_populate returns a json_type you have to use (row).* notation to get individual fields):

CREATE type json_type AS (x1 int, x2 int);

 SELECT id, (json_populate_recordset(null::json_type, json_records)).* FROM my_table;

Answer 2

Here is another example using a json string on PostgreSQL 11

drop TYPE json_test;
create TYPE json_test AS (id_item int, id_menu varchar(100));
select * from json_populate_recordset(null::json_test,'[{"id_item":1,"id_menu":"34"},{"id_item":2,"id_menu":"35"}]')