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, 0, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }
    ');
1 rows affected

select  
	my_table.details->>'city',  u.quantities, u.prices  
	from my_table
	JOIN LATERAL UNNEST( 
		ARRAY(SELECT json_array_elements_text(details->'quantities')) ,
		ARRAY(SELECT json_array_elements_text(details->'prices')) 
	) u(quantities, prices) ON TRUE
	WHERE
	my_table.details->>'city' = 'London'


?column?
quantities
prices


London
112
4

London
145
4

London
222
4

London
122
0

London
124
3