set-returning functions are not allowed in WHERE Postgres

Question

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.

@lancegoh I don't see where your query ever references TableB. Did you mean to say a single table, TableA with two records in it? — Miles Elam
Just realized "records" is an overloaded term in this case. I meant "TableA with two separate rows, each with a jsonb array of records." — Miles Elam

Miles Elam Miles Elam · Accepted Answer · 2020-01-06T18:22:59

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.

set-returning functions are not allowed in WHERE Postgres

1 Answers