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 t (
    id int,
    jcol json
);

INSERT INTO t VALUES
(1 ,      '{"name":"Jack","address":"HNO 123 UK"}'),
(1  ,     '{"address2":"STREET1"}'),
(1   ,    '{"address3":"UK"}');

SELECT * FROM t

CREATE TABLE



INSERT 0 3




id
jcol


1
{"name":"Jack","address":"HNO 123 UK"}

1
{"address2":"STREET1"}

1
{"address3":"UK"}



SELECT 3


SELECT
    *
FROM
    t,
    json_each(jcol)


id
jcol
key
value


1
{"name":"Jack","address":"HNO 123 UK"}
name
"Jack"

1
{"name":"Jack","address":"HNO 123 UK"}
address
"HNO 123 UK"

1
{"address2":"STREET1"}
address2
"STREET1"

1
{"address3":"UK"}
address3
"UK"



SELECT 4


SELECT
    id,
    json_object_agg(key, value)
FROM
    t,
    json_each(jcol)
GROUP BY id


id
json_object_agg


1
{ "name" : "Jack", "address" : "HNO 123 UK", "address2" : "STREET1", "address3" : "UK" }



SELECT 1

id	jcol
1	{"name":"Jack","address":"HNO 123 UK"}
1	{"address2":"STREET1"}
1	{"address3":"UK"}

id	jcol	key	value
1	{"name":"Jack","address":"HNO 123 UK"}	name	"Jack"
1	{"name":"Jack","address":"HNO 123 UK"}	address	"HNO 123 UK"
1	{"address2":"STREET1"}	address2	"STREET1"
1	{"address3":"UK"}	address3	"UK"