I have a table below
| Column | Type |
|---|---|
| id | integer |
| caseworker | uuid |
| data | jsonb |
which contains the following data
| id | caseworker | data |
|---|---|---|
| 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",}} |
I'm quite new to PostgreSQL query and JSON and would like to get an output as something like below. Is it possible to do it in a single query (and if not maybe use a WITH AS () to create the 'temp' table and then query that temp table)? I've been playing with both STRING_AGG and jsonb_array_elements but I can't get the output below that I want.
| caseworker | Case Number | Claimant | Clm_Loc | Clm_Unavail_Dates | Defendant | Def_Loc | Def_Unavail_Dates |
|---|---|---|---|---|---|---|---|
| sally | 001 | Barbara | Manchester | "2022-01-24", "2022-01-25" | Mariah | Manchester | "2022-01-24", "2022-01-25", "2022-01-26" |
| jason | 002 | Michael | London | "2022-02-24", "2022-02-25" | Killian | London | "2022-02-24", "2022-02-25", "2022-02-26" |
| william | 003 | Lisa | Birmingham | Larry | London | "2022-02-24", "2022-02-25", "2022-02-26" | |
| ricky | 004 | Leon | Isle of Man | "2022-02-24", "2022-02-25", "2022-02-26" | Cillian | Birmingham | |
| megan | 005 | Gavin | Bristol | John | Isle of Man |
Hope someone can help :).
CodePudding user response：
If you can live with a JSON array for the dates, this is quite easy.
Use ->> to extract a key value as a string. The dates can be extracted as a JSON array using jsonb_path_query_array()
select 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