Postgres 12

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 (
    mydata jsonb
);

INSERT INTO mytable VALUES ('{"a":[{"b":1,"c":2,"d":3},{"b":4,"c":5,"d":6}], "g":[{"b":1,"c":2,"d":3},{"b":4,"c":5,"d":6}]}');

CREATE TABLE



INSERT 0 1


SELECT
    *
FROM
    mytable,
    jsonb_each(mydata) elems


mydata
key
value


{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]



SELECT 2


SELECT
    *
FROM
    mytable,
    jsonb_each(mydata) elems,
    jsonb_array_elements(elems.value) AS a_elems


mydata
key
value
value


{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]
{"b": 1, "c": 2, "d": 3}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]
{"b": 4, "c": 5, "d": 6}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]
{"b": 1, "c": 2, "d": 3}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]
{"b": 4, "c": 5, "d": 6}



SELECT 4


SELECT
    mydata,
    key,
    a_elems.value - 'c' as a
FROM
    mytable,
    jsonb_each(mydata) elems,
    jsonb_array_elements(elems.value) AS a_elems


mydata
key
a


{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
{"b": 1, "d": 3}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
{"b": 4, "d": 6}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
{"b": 1, "d": 3}

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
{"b": 4, "d": 6}



SELECT 4


SELECT
    mydata,
    key,
    jsonb_agg(a_elems.value - 'c') as a
FROM
    mytable,
    jsonb_each(mydata) elems,
    jsonb_array_elements(elems.value) AS a_elems
GROUP BY mydata, key


mydata
key
a


{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
a
[{"b": 1, "d": 3}, {"b": 4, "d": 6}]

{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}
g
[{"b": 1, "d": 3}, {"b": 4, "d": 6}]



SELECT 2


SELECT
    jsonb_object_agg(key, a)
FROM (
    SELECT
        mydata,
        key,
        jsonb_agg(a_elems.value - 'c') as a
    FROM
        mytable,
        jsonb_each(mydata) elems,
        jsonb_array_elements(elems.value) AS a_elems
    GROUP BY mydata, key
) s
GROUP BY mydata


jsonb_object_agg


{"a": [{"b": 1, "d": 3}, {"b": 4, "d": 6}], "g": [{"b": 1, "d": 3}, {"b": 4, "d": 6}]}



SELECT 1

mydata	key	value
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]

mydata	key	value	value
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]	{"b": 1, "c": 2, "d": 3}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]	{"b": 4, "c": 5, "d": 6}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]	{"b": 1, "c": 2, "d": 3}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	[{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]	{"b": 4, "c": 5, "d": 6}

mydata	key	a
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	{"b": 1, "d": 3}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	{"b": 4, "d": 6}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	{"b": 1, "d": 3}
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	{"b": 4, "d": 6}

mydata	key	a
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	a	[{"b": 1, "d": 3}, {"b": 4, "d": 6}]
{"a": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}], "g": [{"b": 1, "c": 2, "d": 3}, {"b": 4, "c": 5, "d": 6}]}	g	[{"b": 1, "d": 3}, {"b": 4, "d": 6}]