How to add a property to each element of an array in postgresql/jsonb using sql?

Question

PostgreSQL

postgresql table has jsonb format column:

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

how to add "age" property to each element of items array using sql
thanks

Answer 1

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

DEMO