Using json_populate_recordset without creating a table?

Question

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

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 can’t get the request to work:

 select json_populate_recordset(json_records) from my_table 

A few examples that I saw with json_populate_recordset insert the results into the table, but I'm just trying to return the results. Is there a way to do this without creating a new table?

Answer 1

You need to create a new type for the function transfer (note that when returning json_populate a json_type you need to use the notation (row).* 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;