Postgres 13

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?.

-- jsonb in Postgres 9.4 or later
CREATE TABLE reports (data jsonb);
INSERT INTO reports
VALUES ('{"objects":[{"src":"foo.png"}
        ,{"src":"bar.png"}]
        ,"background":"background.png"
        ,"created_at":"2015-04-22 13:33"
        }');

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

-- json in Postgre 9.3
CREATE TABLE reports_93 (data json);
INSERT INTO reports_93
VALUES ('{"objects":[{"src":"foo.png"}
        ,{"src":"bar.png"}]
        ,"background":"background.png"
        ,"created_at":"2015-04-22 13:33"
        }');

CREATE TABLE

INSERT 0 1

CREATE INDEX

CREATE TABLE

INSERT 0 1

-- Postgres 9.4 or later
SELECT * FROM reports
WHERE  data->'objects' @> '[{"src":"foo.png"}]';

data

{"objects": [{"src": "foo.png"}, {"src": "bar.png"}], "background": "background.png", "created_at": "2015-04-22 13:33"}

SELECT 1

-- Postgre 9.3
SELECT data::text, obj
FROM   reports_93 r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

data
obj

{"objects":[{"src":"foo.png"}
        ,{"src":"bar.png"}]
        ,"background":"background.png"
        ,"created_at":"2015-04-22 13:33"
        }
{"src":"foo.png"}

SELECT 1

-- Postgres 9.3, for just a single level of nesting:
SELECT data::text, obj
FROM   reports_93 r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

data
obj

{"objects":[{"src":"foo.png"}
        ,{"src":"bar.png"}]
        ,"background":"background.png"
        ,"created_at":"2015-04-22 13:33"
        }
{"src":"foo.png"}

SELECT 1