set-returning functions are not allowed in WHERE Postgres
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.
lancegoh
Updated on June 09, 2022Comments
-
lancegoh almost 2 years
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.
-
a_horse_with_no_name over 4 years
in ( select * from myfunction(..) )
-
Miles Elam over 4 years@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 over 4 yearsJust realized "records" is an overloaded term in this case. I meant "TableA with two separate rows, each with a jsonb array of records."
-
Master Yoda over 3 yearsyou solve this ?...facing same error
-