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, js jsonb);
INSERT INTO tbl VALUES
  (0, NULL)
, (1, '{}')
, (2, '{"no": "match", "found": "here"}')
, (3, '{"firstname": "John"}')
, (4, '{"secondname": "Smith"}')
, (5, '{"firstname": "John", "secondname": "Smith", "age": 55, "foo": "bar"}')
, (6, '{"firstname": "John", "age": 55, "foo": "bar"}')
, (7, '{"firstname": ["John","Middlename","Thirdname"], "secondname": null, "age": 55, "foo": "bar"}')
, (8, '{"firstname": 1, "secondname": true, "foo": {"bar":"baz", "bam":"bom"}}')
;
9 rows affected

CREATE FUNCTION f_jsonb_filter_keys(js jsonb, VARIADIC keys text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT js - ARRAY (SELECT jsonb_object_keys(js) EXCEPT ALL SELECT unnest(keys))';
SELECT *, js - ARRAY (SELECT jsonb_object_keys(js) EXCEPT ALL VALUES ('firstname'), ('secondname')) AS result1
FROM   tbl;

SELECT *, f_jsonb_filter_keys(js, 'firstname', 'secondname') AS result2
FROM   tbl;

SELECT *, f_jsonb_filter_keys(js, VARIADIC '{firstname, secondname}') AS result3
FROM   tbl;


id
js
result1


0
null
null

1
{}
{}

2
{"no": "match", "found": "here"}
{}

3
{"firstname": "John"}
{"firstname": "John"}

4
{"secondname": "Smith"}
{"secondname": "Smith"}

5
{"age": 55, "foo": "bar", "firstname": "John", "secondname": "Smith"}
{"firstname": "John", "secondname": "Smith"}

6
{"age": 55, "foo": "bar", "firstname": "John"}
{"firstname": "John"}

7
{"age": 55, "foo": "bar", "firstname": ["John", "Middlename", "Thirdname"], "secondname": null}
{"firstname": ["John", "Middlename", "Thirdname"], "secondname": null}

8
{"foo": {"bam": "bom", "bar": "baz"}, "firstname": 1, "secondname": true}
{"firstname": 1, "secondname": true}




id
js
result2


0
null
null

1
{}
{}

2
{"no": "match", "found": "here"}
{}

3
{"firstname": "John"}
{"firstname": "John"}

4
{"secondname": "Smith"}
{"secondname": "Smith"}

5
{"age": 55, "foo": "bar", "firstname": "John", "secondname": "Smith"}
{"firstname": "John", "secondname": "Smith"}

6
{"age": 55, "foo": "bar", "firstname": "John"}
{"firstname": "John"}

7
{"age": 55, "foo": "bar", "firstname": ["John", "Middlename", "Thirdname"], "secondname": null}
{"firstname": ["John", "Middlename", "Thirdname"], "secondname": null}

8
{"foo": {"bam": "bom", "bar": "baz"}, "firstname": 1, "secondname": true}
{"firstname": 1, "secondname": true}




id
js
result3


0
null
null

1
{}
{}

2
{"no": "match", "found": "here"}
{}

3
{"firstname": "John"}
{"firstname": "John"}

4
{"secondname": "Smith"}
{"secondname": "Smith"}

5
{"age": 55, "foo": "bar", "firstname": "John", "secondname": "Smith"}
{"firstname": "John", "secondname": "Smith"}

6
{"age": 55, "foo": "bar", "firstname": "John"}
{"firstname": "John"}

7
{"age": 55, "foo": "bar", "firstname": ["John", "Middlename", "Thirdname"], "secondname": null}
{"firstname": ["John", "Middlename", "Thirdname"], "secondname": null}

8
{"foo": {"bam": "bom", "bar": "baz"}, "firstname": 1, "secondname": true}
{"firstname": 1, "secondname": true}

id	js	result1
0	null	null
1	{}	{}
2	{"no": "match", "found": "here"}	{}
3	{"firstname": "John"}	{"firstname": "John"}
4	{"secondname": "Smith"}	{"secondname": "Smith"}
5	{"age": 55, "foo": "bar", "firstname": "John", "secondname": "Smith"}	{"firstname": "John", "secondname": "Smith"}
6	{"age": 55, "foo": "bar", "firstname": "John"}	{"firstname": "John"}
7	{"age": 55, "foo": "bar", "firstname": ["John", "Middlename", "Thirdname"], "secondname": null}	{"firstname": ["John", "Middlename", "Thirdname"], "secondname": null}
8	{"foo": {"bam": "bom", "bar": "baz"}, "firstname": 1, "secondname": true}	{"firstname": 1, "secondname": true}