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