PostgreSQL create materialized view that sums objects in JSON array into specific columns based on field and table column

Question

Given the following table:

create table entries (
    user_id integer,
    locations jsonb
);

I want to create a materialized view containing the following structure. These columns should start at zero and add up based on what's in locations.

create table entries_locations_extracted (
    user_id integer,
    location_1_a integer,
    location_1_b integer,
    location_2_a integer,
    location_2_b integer
);

Locations will always be a JSON array, with the following structure. Multiple locations may exist in the array. And multiple entries per user may exist.

insert into entries (user_id, locations) values (123, '[
    { location=1, a=1,  b=2 },
    { location=2, a=3,  b=1 },
    { location=2, a=10, b=20 },
    { location=1, a=2,  b=3 },
]')
insert into entries (user_id, locations) values (123, '[
    { location=1, a=100, b=200 },
]')

Given the inserts above. The materialized view should have the following row:

| user_id | location_1_a | location_1_b | location_2_a | location_2_b |
-----------------------------------------------------------------------
| 123     | 103          | 205          | 13           | 21           |

Answer 1

You can use an aggregate with a filter and a lateral query to expand the array for this:

SELECT
    user_id,
    SUM((loc->>'a')::int) FILTER (WHERE loc->'location' = '1') AS location_1_a,
    SUM((loc->>'b')::int) FILTER (WHERE loc->'location' = '1') AS location_1_b,
    SUM((loc->>'a')::int) FILTER (WHERE loc->'location' = '2') AS location_2_a,
    SUM((loc->>'b')::int) FILTER (WHERE loc->'location' = '2') AS location_2_b
FROM
    entries,
    jsonb_array_elements(locations) AS loc
GROUP BY
    user_id;