Search code examples
node.jspostgresqlpsqlpg

Postgresql jsonb -> invalid reference to FROM-clause entry for table "mt"

So I'm trying to inner join multiple tables in order to bind jsonb with a name. But I'm getting this error.

ERROR: invalid reference to FROM-clause entry for table "mt"

Find the recreational fiddle of the problem below.

SELECT test,jsonb_build_object(
    'myData_updated',
    json_agg(elems || jsonb_build_object('product_name', po.name))
)

FROM mainTable mt,
jsonb_array_elements(mt.myData) AS heading_elems,
jsonb_array_elements(heading_elems -> 'pItems') AS elems

JOIN products po ON (elems ->> 'pid' )::int = po.pid

INNER JOIN clients client ON client.client_id = mt.client_id
INNER JOIN projects project on project.project_id = mt.project_id

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=63e5b8a49940bb50b5bb7985a947c09e

I have tried removing alias i still get the same error. Quick googling says it is caused because of JOIN & ", delimited FROM" query

Solution

  • The syntax

    table,
json_array_elements()

    is the shortcut for

    table CROSS JOIN LATERAL
json_array_elements()

    So with the , syntax you used is an implicit join. After that, with using INNER JOIN you are using an explicit join. The mix is not always working, so replace the implicit syntax with an explicit one and it works.

    demo:db<>fiddle

    Beside this, the used function json_agg() is an aggregate, so if you want to get other columns like test you have to do a GROUP BY and/or use more aggregate functions on these columns.