Postgres 14

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 test_json (

name text

, name_adds text

, additional text

);

INSERT INTO test_json (additional, name, name_adds) VALUES

('{"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}],"non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 400, "value_2": 0.1}]}', 'john', 'john')

, ('{"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}],"non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 400, "value_2": 0.1}]}', 'john', 'doe')

, ('[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]', 'downy', 'downy')

, ('[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]', 'downy', 'dan')

;

CREATE TABLE

INSERT 0 4

SELECT a.name, a.name_adds, sub.*

FROM test_json a

CROSS JOIN LATERAL (

SELECT sum((obj ->> 'value_1')::int) AS sum_result -- or numeric?

FROM json_array_elements(COALESCE(CASE WHEN name = name_adds

THEN additional::json -> 'default'

ELSE additional::json -> 'non_default' END

, additional::json)) obj

) sub;

name	name_adds	sum_result
john	john	300
john	doe	600
downy	downy	11
downy	dan	11

SELECT 4