Postgres 10

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 students (
  name VARCHAR(100),
  attributes JSON
);

CREATE TABLE


INSERT INTO students (name,attributes) VALUES ('Ingo',
'[{"name":"Attribute 1","value":"Value 1"},{"name":"Attribute 2","value":"Value 2"}]'
)

INSERT 0 1


select DISTINCT ON (name) s.*,j.attr from 
students  s 
cross join lateral json_array_elements ( attributes ) WITH ORDINALITY as j(attr,id)
WHERE j.attr->>'value' like 'Value%'
ORDER BY name,j.id


name
attributes
attr


Ingo
[{"name":"Attribute 1","value":"Value 1"},{"name":"Attribute 2","value":"Value 2"}]
{"name":"Attribute 1","value":"Value 1"}



SELECT 1