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 my_table(
    details JSON
);

INSERT INTO my_table(details) VALUES('
    {
        "city": "London",
        "name": "Sainburry",
        "quantities": [112, 145, 222, 122, 124],
        "prices": [4, 4, 4, 2, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }'),
    ('{
        "city": "London",
        "name": "Forever",
        "quantities": [33, 44, 432, 134, 57],
        "prices": [5, 4, 7, 8, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }'),
    ('{
        "city": "Edinburgh",
        "name": "Circle",
        "quantities": [22, 44, 234, 123, 66],
        "prices": [5, 2, 8, 4, 6],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }
    ');
3 rows affected

SELECT
    details->>'city' as city,
    SUM(price::numeric) as total_prices
FROM my_table,
    LATERAL json_array_elements_text(details->'prices') AS prices(price)
GROUP BY city


city
total_prices


Edinburgh
25

London
44