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

Question

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

@a_horse_with_no_name Is there a rule for when it's working? Because in this case (dbfiddle.uk/…) the implicit syntax has been chosen, followed by an INNER JOIN — S-Man

S-Man S-Man · Accepted Answer · 2019-08-15T17:05:08

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.

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

1 Answers