Как добавить свойство в каждый элемент массива в postgresql /jsonb при помощи sql?
в таблице postgresql есть колонка формата jsonb:
{ "items": [ {"name": "Bob", ...}, {"name": "Mark", ...}, ... ] } |
{ "items": [ {"name": "Bob", ...}, {"name": "Mark", ...}, ... ] }
как добавить свойство "age" в каждый элемент массива items при помощи sql
спасибо
Дополнительно:
1. Прекратить использовать JSON.
2. Написать код который вытащит данные из базы, обновит JSON и сохранит взад
Хотя куда как проще преобразовать в текст, заменить {"name": на {"age": 25, "name":, да обратно в JSON.
А ещё лучше - хорошо подумать, не пора ли от JSON перейти к вменяемой нормализованной структуре.
желательно сделать через sql запрос, так как это миграция
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) {}; |
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) {};
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 |
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
- Akina можно немного проще, скажем, как будет выглядеть запрос, если мне, каждому элементу массива, нужно добавить поле со значением null
{ "items": [ {"name": "Bob", "age": null, ...}, {"name": "Mark", "age": null, ...}, ... ] }
{ "items": [ {"name": "Bob", "age": null, ...}, {"name": "Mark", "age": null, ...}, ... ] }
хочу разобраться, спасибо за помощь))
- Nikolai Khoziashev, а какая разница, с каким значением? кстати, в DEMO fiddle в одну из записей как раз добавляется null.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для того чтобы добавить свойство в каждый элемент массива в PostgreSQL с типом данных JSONB при помощи SQL, можно воспользоваться функцией jsonb_set(). Эта функция позволяет обновить значение по указанному пути в JSONB объекте.
Прежде всего, необходимо понять структуру вашего JSONB объекта и определить путь, по которому нужно добавить новое свойство в каждый элемент массива. После этого можно использовать следующий SQL запрос:
UPDATE your_table SET your_column = jsonb_set(your_column, '{path}', your_value, true)
Здесь необходимо заменить your_table на название вашей таблицы, your_column на название столбца с JSONB массивом, path на путь к свойству, которое вы хотите добавить, и your_value на значение нового свойства.
Например, если у вас есть таблица users с столбцом data, содержащим массив JSONB объектов, и вы хотите добавить свойство "age" со значением 30 в каждый элемент массива, запрос будет выглядеть примерно так:
UPDATE users SET data = jsonb_set(data, '{age}', '30', true)
После выполнения этого запроса, в каждом элементе массива JSONB объектов в столбце data будет добавлено свойство "age" со значением 30.
Убедитесь, что ваш запрос корректно отражает структуру вашего JSONB объекта и путь к свойству, которое вы хотите добавить. Перед выполнением запроса также рекомендуется создать резервную копию данных, чтобы избежать потери информации в случае ошибки.