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 asset_quantities (
id int,
asset_type text,
quantity int,
site_id int,
asset_ids_json json
);
INSERT INTO asset_quantities VALUES
(1, 'Container', 3, 1, '[{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}]'),
(2, 'Cage', 3, 1, '[{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}]'),
(3, 'Crate', 3, 1, '[{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}]');
SELECT * FROM asset_quantities;
3 rows affected
| id | asset_type | quantity | site_id | asset_ids_json |
|---|---|---|---|---|
| 1 | Container | 3 | 1 | [{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}] |
| 2 | Cage | 3 | 1 | [{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}] |
| 3 | Crate | 3 | 1 | [{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}] |
SELECT
*
FROM asset_quantities aq,
json_array_elements(asset_ids_json)
| id | asset_type | quantity | site_id | asset_ids_json | value |
|---|---|---|---|---|---|
| 1 | Container | 3 | 1 | [{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}] | {"id":1,"make":"am1","model":"amo1"} |
| 1 | Container | 3 | 1 | [{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}] | {"id":2,"make":"am1","model":"amo2"} |
| 1 | Container | 3 | 1 | [{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}] | {"id":3,"make":"am3","model":"amo3"} |
| 2 | Cage | 3 | 1 | [{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}] | {"id":4,"make":"bm1","model":"bmo1"} |
| 2 | Cage | 3 | 1 | [{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}] | {"id":5,"make":"bm2","model":"bmo2"} |
| 2 | Cage | 3 | 1 | [{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}] | {"id":6,"make":"bm2","model":"cmo3"} |
| 3 | Crate | 3 | 1 | [{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}] | {"id":7,"make":"cm1","model":"cmo1"} |
| 3 | Crate | 3 | 1 | [{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}] | {"id":8,"make":"cm1","model":"cmo1"} |
| 3 | Crate | 3 | 1 | [{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}] | {"id":9,"make":"cm1","model":"cmo2"} |
SELECT
site_id,
asset_type,
quantity
FROM asset_quantities aq,
json_array_elements(asset_ids_json)
WHERE value ->> 'make' = 'cm1'
| site_id | asset_type | quantity |
|---|---|---|
| 1 | Crate | 3 |
| 1 | Crate | 3 |
| 1 | Crate | 3 |
SELECT DISTINCT ON (id)
site_id,
asset_type,
quantity
FROM asset_quantities aq,
json_array_elements(asset_ids_json)
WHERE value ->> 'make' = 'cm1'
| site_id | asset_type | quantity |
|---|---|---|
| 1 | Crate | 3 |
SELECT
site_id,
SUM(case when asset_type = 'Container' then quantity end) container_qty,
SUM(case when asset_type = 'Cage' then quantity end) cage_qty ,
SUM(case when asset_type = 'Crate' then quantity end) crate_qty
FROM (
SELECT DISTINCT ON (id)
site_id,
asset_type,
quantity
FROM asset_quantities aq,
json_array_elements(asset_ids_json)
WHERE value ->> 'make' = 'cm1'
) s
GROUP BY site_id
| site_id | container_qty | cage_qty | crate_qty |
|---|---|---|---|
| 1 | null | null | 3 |