sqljsonbgreenplum

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)

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.

Solution

  • 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