Postgres 16

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 "myTable"(id int generated always as identity primary key,
                       "myColumn" jsonb);
insert into "myTable"("myColumn") values
('{"foo": {"key0": {"bar": "myValueA"},
           "key1": {"bar": "myValueB"},
           "keyN": {"bar": "myValueN"}
          }
}'),
('{"foo": {"key123": {"bar": "myValueC"},
           "key345": {"bar": "myValueD"}
          }
}'),
('{"foo": {"XYZ": {"bar": "myValueN"}
          }
}')
returning id,jsonb_pretty("myColumn");

CREATE TABLE




id
jsonb_pretty


1
{
    "foo": {
        "key0": {
            "bar": "myValueA"
        },
        "key1": {
            "bar": "myValueB"
        },
        "keyN": {
            "bar": "myValueN"
        }
    }
}

2
{
    "foo": {
        "key123": {
            "bar": "myValueC"
        },
        "key345": {
            "bar": "myValueD"
        }
    }
}

3
{
    "foo": {
        "XYZ": {
            "bar": "myValueN"
        }
    }
}



INSERT 0 3


/*
I would like to get all the records that have 
the text "myValueX" among all the paths 'foo' > ANY_KEY > 'bar'

It could be 'foo' > 'key0' > 'bar' or 'foo' > 'key1' > 'bar' or any keyN.

In "pseudo" PostgreSQL:

SELECT * FROM "myTable" WHERE "myColumn" #>> '{foo,*,bar}' = 'myValue';
where * would mean "can be any key".
*/
select * from "myTable" 
where "myColumn" @@ '$.foo.*.bar=="myValueN"';


id
myColumn


1
{"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}}

3
{"foo": {"XYZ": {"bar": "myValueN"}}}



SELECT 2

id	jsonb_pretty
1	{ "foo": { "key0": { "bar": "myValueA" }, "key1": { "bar": "myValueB" }, "keyN": { "bar": "myValueN" } } }
2	{ "foo": { "key123": { "bar": "myValueC" }, "key345": { "bar": "myValueD" } } }
3	{ "foo": { "XYZ": { "bar": "myValueN" } } }

id	myColumn
1	{"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}}
3	{"foo": {"XYZ": {"bar": "myValueN"}}}