CODEWITHSUNDEEP
arraysjsonpostgresql
Dennis Bauszus
Dennis Bauszus

Reputation: 1792

Is it possible to use json_populate_recordset in a subquery?

I have a json array with a couple of records, all of which have 3 fields lat, lon, v.

I would like to create a select subquery from this array to join with another query. The problem is that I cannot make the example in the PostgreSQL documentation work.

select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')

Should result in:

 a | b
---+---
 1 | 2
 3 | 4

But I only get ERROR: type "x" does not exist Position: 45

Upvotes: 2

Views: 2178

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

It is necessary to pass a composite type to json_populate_recordset whereas a column list is passed to json_to_recordset:

select *
from json_to_recordset('[{"a":1,"b":2},{"a":3,"b":4}]') x (a int, b int)
;
 a | b 
---+---
 1 | 2
 3 | 4

Upvotes: 2

Related Questions