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"}]} |