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.
