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}]}');
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')
1 rows affected
4 rows affected
SELECT m.*, obj
FROM jsontable t, jsonb_array_elements(t.response -> 'SCS') obj
JOIN message m ON m.msg_id = (obj->>'referenceId')::int AND m.status = 1;
| msg_id | status | content | obj |
|---|---|---|---|
| 215 | 1 | Test4 | {"customerId": 100, "referenceId": 215} |
| 544 | 1 | Test | {"customerId": 120, "referenceId": 544} |
SELECT m.*, obj
FROM jsontable t,
LATERAL jsonb_array_elements(t.response -> 'SCS') obj
JOIN message m ON m.msg_id = (obj->>'referenceId')::int
WHERE m.status = 1;
| msg_id | status | content | obj |
|---|---|---|---|
| 215 | 1 | Test4 | {"customerId": 100, "referenceId": 215} |
| 544 | 1 | Test | {"customerId": 120, "referenceId": 544} |