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

CREATE TABLE tbl (id int, jdata jsonb);

INSERT INTO tbl VALUES
  ( 1, '{"name": "Somedata",
         "array": [{"name":"bla1", "attr": "somevalue"},
                   {"name":"bla2", "otherdata": "somevalue2"},
                   {"name":"bla3", "otherdata": "somevalue"}
                  ],
         "array2": ["bla21","bla20","bla22","bla23"],
         "otherstuff": "stuff"}')
-- row with "attr": null
, ( 2, '{"name": "Somedata",
         "array": [{"name":"bla0", "attr": null}, 
                   {"name":"bla2", "otherdata": "somevalue2"},
                   {"name":"bla3", "otherdata": "somevalue"}
                  ],
         "array2": ["bla21","bla20","bla22","bla23"],
         "otherstuff": "stuff"}')
-- row with no "attr" key
, ( 3, '{"name": "Somedata",
         "array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"name":"bla2", "otherdata": "somevalue2"},
                   {"name":"bla3", "otherdata": "somevalue"}
                  ],
         "array2": ["bla21","bla20","bla22","bla23"],
         "otherstuff": "stuff"}')
-- with JSON primitive "attr" in array
, ( 4, '{"name": "Somedata",
         "array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"name":"bla2", "otherdata": "somevalue2"},
                   {"name":"bla3", "otherdata": "somevalue"},
                   "attr"
                  ],
         "array2": ["bla21","bla20","bla22","bla23"],
         "otherstuff": "stuff"}')
-- with nested array contaiing "attr" record
, ( 5, '{"name": "Somedata",
         "array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"name":"bla2", "otherdata": "somevalue2"},
                   {"name":"bla3", "otherdata": "somevalue"},
                   [ {"name":"bla1", "attr": "somevalue"}]
                  ],
         "array2": ["bla21","bla20","bla22","bla23"],
         "otherstuff": "stuff"}')
        
-- with nested record containing "attr" record
, ( 6, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"nested": {"name":"bla1", "attr": "somevalue"}}
                  ]}')
-- with nested record containing array containing "attr" record
, ( 7, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"nested": [{"name":"bla1", "attr": "somevalue"}]}
                  ]}')
-- with nested record containing array containing array with "attr" primitive
, ( 8, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"nested": [{"name":"bla1", "foo": ["attr"]}]}
                  ]}')
-- with nested record containing array containing array with "attr" key
, ( 9, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   {"nested": [{"name":"bla1", "foo": [{"attr":"bar"}]}]}
                  ]}')
-- with nested array containing array containing array with "attr" key
, (10, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   [[[{"attr":"bar"}]]]
                  ]}')
-- with nested array containing record containing array with "attr" key
, (11, '{"array": [{"name":"bla0", "non-attr": "somevalue"}, 
                   [[{"foo":1, "bar":[{"attr":"bar"}]}]]
                  ]}');
        
CREATE INDEX tbl_jdata_array_idx ON tbl USING GIN ((jdata->'array'));  --  default jsonb_ops

CREATE TABLE



INSERT 0 11



CREATE INDEX


-- Postgres 12+ with SQL/JSON:
-- jsonpath expression testing for any key named 'attr':
SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (exists (@."attr"))';


id
jdata


1
{"name": "Somedata", "array": [{"attr": "somevalue", "name": "bla1"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

2
{"name": "Somedata", "array": [{"attr": null, "name": "bla0"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

5
{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, [{"attr": "somevalue", "name": "bla1"}]], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}



SELECT 3


-- equivalent, because "lax" is the default mode (arrays are abstracted away automatically)
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'lax $[*] ? (exists (@."attr"))';


id
jdata


1
{"name": "Somedata", "array": [{"attr": "somevalue", "name": "bla1"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

2
{"name": "Somedata", "array": [{"attr": null, "name": "bla0"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

5
{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, [{"attr": "somevalue", "name": "bla1"}]], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}



SELECT 3


-- array not abstracted away
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'strict $[*] ? (exists (@."attr"))';


id
jdata


1
{"name": "Somedata", "array": [{"attr": "somevalue", "name": "bla1"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

2
{"name": "Somedata", "array": [{"attr": null, "name": "bla0"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}



SELECT 2


-- recursively with the .** acsessor
-- "lax" would be equivalent, but inefficient, doing twice the work
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'strict $.** ? (exists (@."attr"))';


id
jdata


1
{"name": "Somedata", "array": [{"attr": "somevalue", "name": "bla1"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

2
{"name": "Somedata", "array": [{"attr": null, "name": "bla0"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

5
{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, [{"attr": "somevalue", "name": "bla1"}]], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

6
{"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": {"attr": "somevalue", "name": "bla1"}}]}

7
{"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": [{"attr": "somevalue", "name": "bla1"}]}]}

9
{"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": [{"foo": [{"attr": "bar"}], "name": "bla1"}]}]}

10
{"array": [{"name": "bla0", "non-attr": "somevalue"}, [[[{"attr": "bar"}]]]]}

11
{"array": [{"name": "bla0", "non-attr": "somevalue"}, [[{"bar": [{"attr": "bar"}], "foo": 1}]]]}



SELECT 8


-- find values (scalar, primitive) instead of keys
SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (@ == "attr")';


id
jdata


4
{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, "attr"], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}



SELECT 1


-- find values recursively:
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'strict $.** ? (@ == "attr")';


id
jdata


4
{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, "attr"], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

8
{"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": [{"foo": ["attr"], "name": "bla1"}]}]}



SELECT 2


-- Index used?
SET enable_seqscan = off;  -- to show index usage in mini-table
-- looking for keys
EXPLAIN
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'strict $.** ? (exists (@."attr"))';

SET




QUERY PLAN


Bitmap Heap Scan on tbl  (cost=68.00..72.02 rows=1 width=36)

  Recheck Cond: ((jdata -> 'array'::text) @? 'strict $.**?(exists (@."attr"))'::jsonpath)

  ->  Bitmap Index Scan on tbl_jdata_array_idx  (cost=0.00..68.00 rows=1 width=0)

        Index Cond: ((jdata -> 'array'::text) @? 'strict $.**?(exists (@."attr"))'::jsonpath)



EXPLAIN


-- looking for values
EXPLAIN
SELECT *
FROM   tbl
WHERE  jdata->'array' @? 'strict $.** ? (@ == "attr")';


QUERY PLAN


Bitmap Heap Scan on tbl  (cost=12.00..16.02 rows=1 width=36)

  Recheck Cond: ((jdata -> 'array'::text) @? 'strict $.**?(@ == "attr")'::jsonpath)

  ->  Bitmap Index Scan on tbl_jdata_array_idx  (cost=0.00..12.00 rows=1 width=0)

        Index Cond: ((jdata -> 'array'::text) @? 'strict $.**?(@ == "attr")'::jsonpath)



EXPLAIN

id	jdata
1	{"name": "Somedata", "array": [{"attr": "somevalue", "name": "bla1"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}
2	{"name": "Somedata", "array": [{"attr": null, "name": "bla0"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}
5	{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, [{"attr": "somevalue", "name": "bla1"}]], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}

id	jdata
4	{"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"}, {"name": "bla3", "otherdata": "somevalue"}, "attr"], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}
8	{"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": [{"foo": ["attr"], "name": "bla1"}]}]}