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

drop table if exists survey_results;
create table survey_results(id serial primary key, name text, email text, payload json);
insert into survey_results (name, email, payload) values
(
    'Ying',
    'SmartStuff@gmail.com',
    '[
        {"fieldName":"Product Name", "Value":"Calculator"},
        {"fieldName":"Product Price", "Value":"$54.99"}
    ]'),
(
    'Kendrick',
    'MrTexas@gmail.com',
    '[
        {"fieldName":"Food Name", "Value":"Texas Toast"},
        {"fieldName":"Food Taste", "Value":"Delicious"}
    ]'),
(
    'Andy',
    'WhereTheBass@gmail.com',
    '[
        {"fieldName":"Band Name", "Value":"MetalHeads"},
        {"fieldName":"Valid Member", "Value":"TRUE"}
    ]');

DROP TABLE



CREATE TABLE



INSERT 0 3


select distinct value->>'fieldName' as field_name
from survey_results
cross join json_array_elements(payload)


field_name


Product Price

Food Name

Valid Member

Food Taste

Band Name

Product Name



SELECT 6


select distinct value->>'Value' as json_value
from survey_results
cross join json_array_elements(payload)
where value->>'fieldName' = 'Food Name'


json_value


Texas Toast



SELECT 1