Как добавить свойство в каждый элемент массива в postgresql /jsonb при помощи sql?

Question

[Nikolai Khoziashev]

в таблице postgresql есть колонка формата jsonb:

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

как добавить свойство "age" в каждый элемент массива items при помощи sql
спасибо

Comments

[Slava Rozhnev]

1. Прекратить использовать JSON.
2. Написать код который вытащит данные из базы, обновит JSON и сохранит взад

[Nikolai Khoziashev]

Slava Rozhnev, да, я это понимаю, но как?

[Akina]

Выделить массив из JSON, поделить на объекты, каждый отдельный объект конкатенировать с дополнением, собрать обратно в массив, вставить массив в исходный JSON.

Хотя куда как проще преобразовать в текст, заменить {"name": на {"age": 25, "name":, да обратно в JSON.

[Slava Rozhnev]

Nikolai Khoziashev, Akina, Я так понимаю что значение поля age будет различным для каждого объекта?

[Slava Rozhnev]

Nikolai Khoziashev, какой ЯП предпочитаете? JS, PHP, Python?

[Nikolai Khoziashev]

Slava Rozhnev, js, да, значения будут различные

[Akina]

Выгрузить в CSV, обработать, загрузить обратно. Вот точно быстрее будет...

А ещё лучше - хорошо подумать, не пора ли от JSON перейти к вменяемой нормализованной структуре.

[Nikolai Khoziashev]

Akina, все немного сложнее, чем кажется, так, как уже есть данные в бд и именно в формате jsonb...
желательно сделать через sql запрос, так как это миграция

[Nikolai Khoziashev]

Akina, вот допустим пример, как просто впихнуть данные в jsonb

exports.up = async function(knex) {
  await knex.raw(`UPDATE table_name SET column_name = jsonb_set(column_name, '{new_item}', 'null'::jsonb);`);
};

exports.down = async function(knex) {};

Answer 1

[Akina]

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

Comments

[Nikolai Khoziashev]

Akina можно немного проще, скажем, как будет выглядеть запрос, если мне, каждому элементу массива, нужно добавить поле со значением null

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

хочу разобраться, спасибо за помощь))

[Akina]

Nikolai Khoziashev, а какая разница, с каким значением? кстати, в DEMO fiddle в одну из записей как раз добавляется null.