I'm trying to display the contents of the jsonb column using the jsonb_to_recordset function: But get the following error The function itself works when I substitute the values manually. Сan't figure out what the problem is. Answer Write it as a FROM...JOIN:

SQL error 42P01 when using jsonb_to_recordset function

I’m trying to display the contents of the jsonb column using the jsonb_to_recordset function:

SELECT id, city
FROM jsonb_to_recordset("sometable".data) AS t(id int4, city varchar)

​x
 
SELECT id, cityFROM jsonb_to_recordset("sometable".data) AS t(id int4, city varchar)​

But get the following error

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "sometable"

 
SQL Error [42P01]: ERROR: missing FROM-clause entry for table "sometable"​

The function itself works when I substitute the values manually. Сan’t figure out what the problem is.

Answer

Write it as a FROM...JOIN:

-- inner join
SELECT t.id, t.city
FROM sometable
CROSS JOIN LATERAL jsonb_to_recordset(sometable.data) AS t(id int4, city varchar)

-- outer join, include rows where jsonb is null
SELECT t.id, t.city
FROM sometable
LEFT JOIN LATERAL jsonb_to_recordset(sometable.data) AS t(id int4, city varchar) ON true

 
-- inner joinSELECT t.id, t.cityFROM sometableCROSS JOIN LATERAL jsonb_to_recordset(sometable.data) AS t(id int4, city varchar)​-- outer join, include rows where jsonb is nullSELECT t.id, t.cityFROM sometableLEFT JOIN LATERAL jsonb_to_recordset(sometable.data) AS t(id int4, city varchar) ON true​

Advertisement

Answer