Version PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit
I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values from others, and non JSONb columns.
Table Definition (I have removed other columns that are not relevant)
id varchar(255) NOT NULL,
casualties jsonb NOT NULL,
involved_parties jsonb NULL,
tags jsonb NULL,
reported_at int8 NULL,
casualities
Each row has a single object which represents the count of casualties across categories, for the incident.
{"police_deaths": 0, "civilian_deaths": 0, "criminal_deaths": 0, "military_deaths": 0, "police_injuries": 0, "emergency_deaths": 0, "civilian_injuries": 1, "criminal_injuries": 1, "military_injuries": 0, "emergency_injuries": 0}
involved_parties
This is an array of objects. Each row shows zero or more involved parties (who was involved in the incident). The data looks a little misleading at first as for each entry within the array the involved party/incident relationship has an ID. This doesnt really give us anything and I dont need this, but it is in the data currently.
[
{"id": "2a0fd9dc-40bd-40dc-88ce-bc819fe9cdd8", "type": "group", "group": {"id": "6d342bfc-72c4-4588-ab95-1b3bdfb4881a", "name": "Naxals"}, "involvement": "Actor"},
{"id": "dafc4726-3d3d-40cb-bbaf-63fa57250b44", "type": "group", "group": {"id": "18c6d3f6-c3eb-45db-9a02-26606f85d7eb", "name": "Indian Security Forces"}, "involvement": "Directly Targeted"}
]
Its the group, and involvement data I am interested in here.
affected sectors
This structure is much like the involved parties.
[
{"id": "fcb952ef-3139-4fe7-ba15-7d800bdc60ae", "sector": {"id": "668d330e-aee5-4291-be98-df9c32b5b420", "name": "Military"}},
{"id": "d1b71bae-29ac-48a2-ab41-a6979d720171", "sector": {"id": "550a4aa0-6d6f-4be2-ba33-f35d159ee686", "name": "Police/Law"}}
]
Here it is the sector I am interested in.
reported_at
This is the epoch representation of when the incident was reported by our analysts.
Desired Output
For the records in the query I want a single row. The single row has the following columns:
incident_count,
casualties,
involved_parties,
tags,
min_reported_at,
max_reported_at
The incident count should be just that, the number of rows formally represented.
The casualties object always has the same properties in the JSON and I want to sum them. So, there will be a single object with the sum of all police_deaths, civilian_deaths etc.
For the involved parties and affected sectors; each should have a single array containing a unique set of the parties/sectors from the rows
Min/Max reported at should be the min/max over all rows.
I tried from this starting point:
select
jsonb_agg(incidents.affected_sectors) as affected_sectors,
jsonb_agg(incidents.involved_parties) as involved_parties
from incidents
But this was painfully slow (9 seconds). So I then tried by expanding each object out to a row, then trying to collapse it back but got lost off and bad results.
I'd appreciate any pointers here
Thanks,
Mark.