set-returning functions are not allowed in WHERE Postgres

I am trying to do a join between 2 jsonb array in 2 different tables. The id to link them are inside the arrays.

This is what I have tried, I created a function on postgres that return me a list of Id (from table B) and I am trying to match them to Table A.

TableA
ID | resultA
1 | {records:[{id: 1, key: A, value: High}, {id: 2, key: B, value:Low}]}
TableB
ID | resultB
2 | {records:[{key: A, value: New Equipment}, {key: B, value: Old Equipment}]

So I got the SQL to show me 2 columns for both the Ids

select elem-> 'key' as TableA_id from TableA, lateral jsonb_array_elements(resultA -> 'records') elem
where elem-> 'key' in (public.myfunction('999'))

I am facing the following error: set-returning functions are not allowed in WHERE

Not sure what is wrong here.

1 Answer

While a subselect in the WHERE clause would work, why not perform an INNER JOIN on the set-returning function instead?

SELECT elem->>'key' AS TableA_id
FROM TableA CROSS JOIN LATERAL jsonb_array_elements(TableA.resultA->'records') AS elem INNER JOIN public.myfunction('999') AS func(key) ON (func.key = elem->>'key');

Also, did you really want a jsonb value returned? I can't help but think you meant to return text.

elem->'key' would return a jsonb value while elem->>'key' would return the key as text. My example above returns text as that seemed most appropriate.

If you really want to keep the WHERE clause…

SELECT elem->>'key' AS TableA_id
FROM TableA CROSS JOIN LATERAL jsonb_array_elements(TableA.resultA->'records') AS elem
WHERE elem->>'key IN (SELECT * FROM public.myfunction('999'));

Personally I much prefer the first form with the INNER JOIN.