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 test (id INT, value JSONB);
INSERT INTO test VALUES 
(1, '{"items": [{"name": "Bob"},{"name": "Mark"}]}'),
(2, '{"items": [{"name": "Joe"}, {"name": "Alex"}]}');
SELECT * FROM test;
2 rows affected



id
value


1
{"items": [{"name": "Bob"}, {"name": "Mark"}]}

2
{"items": [{"name": "Joe"}, {"name": "Alex"}]}


SELECT *,
       jae.value_1 || '{"age": 25}' :: JSONB
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)


id
value
value_1
?column?


1
{"items": [{"name": "Bob"}, {"name": "Mark"}]}
{"name": "Bob"}
{"age": 25, "name": "Bob"}

1
{"items": [{"name": "Bob"}, {"name": "Mark"}]}
{"name": "Mark"}
{"age": 25, "name": "Mark"}

2
{"items": [{"name": "Joe"}, {"name": "Alex"}]}
{"name": "Joe"}
{"age": 25, "name": "Joe"}

2
{"items": [{"name": "Joe"}, {"name": "Alex"}]}
{"name": "Alex"}
{"age": 25, "name": "Alex"}


WITH cte AS (
    SELECT 'Bob' AS name, 25 AS age UNION ALL
    SELECT 'Mark', 30 UNION ALL
    SELECT 'Joe', 35
)
SELECT *,
       jae.value_1 || jsonb_build_object('age', cte.age)
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'


id
value
value_1
name
age
?column?


1
{"items": [{"name": "Bob"}, {"name": "Mark"}]}
{"name": "Bob"}
Bob
25
{"age": 25, "name": "Bob"}

1
{"items": [{"name": "Bob"}, {"name": "Mark"}]}
{"name": "Mark"}
Mark
30
{"age": 30, "name": "Mark"}

2
{"items": [{"name": "Joe"}, {"name": "Alex"}]}
{"name": "Joe"}
Joe
35
{"age": 35, "name": "Joe"}

2
{"items": [{"name": "Joe"}, {"name": "Alex"}]}
{"name": "Alex"}
null
null
{"age": null, "name": "Alex"}


WITH cte AS (
    SELECT 'Bob' AS name, 25 AS age UNION ALL
    SELECT 'Mark', 30 UNION ALL
    SELECT 'Joe', 35
)
SELECT test.id,
       jsonb_agg (jae.value_1 || jsonb_build_object('age', cte.age))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.id


id
jsonb_agg


2
[{"age": 35, "name": "Joe"}, {"age": null, "name": "Alex"}]

1
[{"age": 25, "name": "Bob"}, {"age": 30, "name": "Mark"}]


WITH cte AS (
    SELECT 'Bob' AS name, 25 AS age UNION ALL
    SELECT 'Mark', 30 UNION ALL
    SELECT 'Joe', 35
)
SELECT test.id,
       jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.id


id
jsonb_build_object


2
{"items": [{"age": 35, "name": "Joe"}, {"age": null, "name": "Alex"}]}

1
{"items": [{"age": 25, "name": "Bob"}, {"age": 30, "name": "Mark"}]}

id	value
1	{"items": [{"name": "Bob"}, {"name": "Mark"}]}
2	{"items": [{"name": "Joe"}, {"name": "Alex"}]}

id	value	value_1	?column?
1	{"items": [{"name": "Bob"}, {"name": "Mark"}]}	{"name": "Bob"}	{"age": 25, "name": "Bob"}
1	{"items": [{"name": "Bob"}, {"name": "Mark"}]}	{"name": "Mark"}	{"age": 25, "name": "Mark"}
2	{"items": [{"name": "Joe"}, {"name": "Alex"}]}	{"name": "Joe"}	{"age": 25, "name": "Joe"}
2	{"items": [{"name": "Joe"}, {"name": "Alex"}]}	{"name": "Alex"}	{"age": 25, "name": "Alex"}

id	jsonb_agg
2	[{"age": 35, "name": "Joe"}, {"age": null, "name": "Alex"}]
1	[{"age": 25, "name": "Bob"}, {"age": 30, "name": "Mark"}]

id	jsonb_build_object
2	{"items": [{"age": 35, "name": "Joe"}, {"age": null, "name": "Alex"}]}
1	{"items": [{"age": 25, "name": "Bob"}, {"age": 30, "name": "Mark"}]}