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)

But get the following error

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.

CodePudding user response：

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