Postgres 11

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 mytable
    ("sensor" varchar(1), "details" jsonb)
;
    
INSERT INTO mytable
    ("sensor", "details")
VALUES
    ('A', '[{"direction":"up", "result": 1.0}, {"direction":"up", "result": 2.0}]'),
    ('B', '[{"direction":"up", "result": 3.0}, {"direction":"down", "result": 4.0}]'),
    ('B', '[{"direction":"up", "result": 5.0}, {"direction":"up", "result": 6.0}, {"direction":"down", "result": 7.0}]'),
    ('A', '[{"direction":"down", "result": 8.0}, {"direction":"left", "result": 9.0}]')
;
4 rows affected

SELECT 
    sensor,
    SUM((elems ->> 'result')::numeric) AS up_sum
FROM
    mytable,
    jsonb_array_elements(details) elems
WHERE elems ->> 'direction' = 'up'
GROUP BY sensor


sensor
up_sum


B
14.0

A
3.0


SELECT 
    sensor,
    SUM((elems ->> 'result')::numeric)
        FILTER (WHERE elems ->> 'direction' = 'up') AS up_sum,
    SUM((elems ->> 'result')::numeric) 
        FILTER (WHERE elems ->> 'direction' = 'down') AS down_sum
FROM
    mytable,
    jsonb_array_elements(details) elems
GROUP BY sensor


sensor
up_sum
down_sum


B
14.0
11.0

A
3.0
8.0