By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table jsontable (response jsonb);
insert into jsontable values ('{"SCS":[{"customerId": 100, "referenceId": 215}, {"customerId": 120, "referenceId":544}, {"customerId": 400, "referenceId": 177}]}');
1 rows affected
create table message (msg_id integer, status integer, content text);
insert into message values
(544, 1, 'Test'), (134, 1, 'Test2'), (177, 0, 'Test3'), (215, 1, 'Test4')
4 rows affected
SELECT m.*
FROM jsontable t
JOIN message m ON m.msg_id = (jsonb_array_elements(t.response -> 'SCS')->>'referenceId')::int
and m.status = 1
ERROR: set-returning functions are not allowed in JOIN conditions
LINE 4: JOIN message m ON m.msg_id = (jsonb_array_elements(t.respons...
^