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 temp TABLE test_json (
    name text,
    name_adds text,
    additional text,
    created_at timestamp
);

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');

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', 'doe');

INSERT INTO test_json (additional, name, name_adds)
    VALUES ('[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]', 'downy', 'downy');

INSERT INTO test_json (additional, name, name_adds)
    VALUES ('[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]', 'downy', 'dan');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from test_json;
-- SELECT * FROM information_schema.tables;


name
name_adds
additional
created_at


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}]}
null

john
doe
{"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}]}
null

downy
downy
[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]
null

downy
dan
[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]
null



SELECT 4


SELECT 
name, name_adds,
b.default_fix

FROM "test_json" a
CROSS JOIN
(
    SELECT 
    	SUM((v.val->>'value_1')::numeric) as default_fix
    FROM "test_json"
    cross join lateral json_array_elements(additional::json-> 'default') as v(val)
) b


name
name_adds
default_fix


john
john
600

john
doe
600

downy
downy
600

downy
dan
600



SELECT 4

name	name_adds	additional	created_at
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}]}	null
john	doe	{"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}]}	null
downy	downy	[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]	null
downy	dan	[{"value_1": 10, "value_2": 11} ,{"value_1": 1, "value_2": 19}]	null