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 the_table (id int, caseworker text, data jsonb);
insert into the_table
VALUES
(1, 'sally', '{"CaseNumber": "001","claimant": "Barbara","Location": "Manchester","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxb0","value": "2022-01-24"},{"id": "f7f83778-7ab1-4b33-abe5-xxxxxxxxxxf4","value": "2022-01-25"}], "defense":{"defendant": "Mariah","Location": "Manchester","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz0","value": "2022-01-24"},{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz1","value": "2022-01-25"},{"id": "f7f83778-7ab1-4b33-abe5-xxxxxxxxxxz4","value": "2022-01-26"}]}}'),
(2, 'jason', '{"CaseNumber": "002","claimant": "Michael","Location": "London","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxb1","value": "2022-02-24"},{"id": "f7f83778-7ab1-4b33-abe5-xxxxxxxxxxf5","value": "2022-02-25"}],"defense":{"defendant": "Killian","Location": "London","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz1","value": "2022-02-24"},{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz2","value": "2022-02-25"},{"id": "f7f83778-7ab1-4b33-abe5-xxxxxxxxxxz5","value": "2022-02-26"}]}}'),
(3, 'william', '{"CaseNumber": "003","claimant": "Lisa","Location": "Birmingham","defense":{"defendant": "Larry","Location": "London","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz2","value": "2022-02-24"},{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz3","value": "2022-02-25"},{"id": "f7f837787ab1-4b33-abe5-xxxxxxxxxxz6","value": "2022-02-26"}]}}'),
(4, 'ricky', '{"CaseNumber": "004","claimant": "Leon","Location": "Isle of Man","unavailableDates": [{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz3","value": "2022-02-24"},{"id": "0372d8d7-2778-4175-8826-xxxxxxxxxxz5","value": "2022-02-25"},{"id": "f7f83778-7ab1-4b33-abe5-xxxxxxxxxxz7","value": "2022-02-26"}],"defense":{"defendant": "Cillian","Location": "Birmingham"}}'),
(5, 'megan', '{"CaseNumber": "005","claimant": "Gavin","Location": "Bristol","defense":{"defendant": "John","Location": "Isle of Man"}}');
5 rows affected
select id, caseworker,
data ->> 'CaseNumber' as casenumber,
data ->> 'claimant' as claimant,
data ->> 'Location' as clm_loc,
jsonb_path_query_array(data, '$.unavailableDates.value') as Clm_Unavail_Dates,
data -> 'defense' ->> 'defendant' as defendant,
data -> 'defense' ->> 'Location' as def_loc,
jsonb_path_query_array(data, '$.defense.unavailableDates.value') as Def_Unavail_Dates
from the_table;
| id | caseworker | casenumber | claimant | clm_loc | clm_unavail_dates | defendant | def_loc | def_unavail_dates |
|---|---|---|---|---|---|---|---|---|
| 1 | sally | 001 | Barbara | Manchester | ["2022-01-24", "2022-01-25"] | Mariah | Manchester | ["2022-01-24", "2022-01-25", "2022-01-26"] |
| 2 | jason | 002 | Michael | London | ["2022-02-24", "2022-02-25"] | Killian | London | ["2022-02-24", "2022-02-25", "2022-02-26"] |
| 3 | william | 003 | Lisa | Birmingham | [] | Larry | London | ["2022-02-24", "2022-02-25", "2022-02-26"] |
| 4 | ricky | 004 | Leon | Isle of Man | ["2022-02-24", "2022-02-25", "2022-02-26"] | Cillian | Birmingham | [] |
| 5 | megan | 005 | Gavin | Bristol | [] | John | Isle of Man | [] |