Добро пожаловать обратно, чтобы узнать больше о jsonb …✨✨✨
После небольшого углубления в jsonb и знакомства с ним и с тем, как добавлять и запрашивать данные, давайте погрузимся в него поглубже, чтобы узнать, как его изменять…
Обновление JSONB
В rails работайте с ним как с хэшем
> book.payload['publisher'] = "new publisher"
> book.save
> book.reload.payload['publisher']
=> "new publisher"
> new_payload = {publisher: "Blue Ocean"}
> book.update(payload: new_payload)
> book.reload.payload
=> {"publisher"=>"Blue Ocean"}
> book.update(payload: {})
но работать с jsonb как с хэшем, чтобы перебирать данные и изменять их, а затем сохранять или обновлять каждую запись нецелесообразно и не функционально!
Если нам нужно обновить так много записей или добавить ключи ко всем записям … нам действительно нужны некоторые инструменты postgres, чтобы помочь здесь …..
Так что давайте спросим у postgres, как мы можем это сделать?
Postgres : конечно, используйте мой оператор update с некоторыми из моих операторов и функций.
Использование оператора update может быть хитрым … так что если мы попробуем что-то вроде
UPDATE books SET payload -> 'publisher' = 'sara'
WHERE title = 'book1';
-- we will get error
ERROR: syntax error at or near "->"
LINE 1: UPDATE books SET payload -> 'publisher' = 'sara' WHERE title…
поэтому давайте начнем с самого начала и с того факта, что полезная нагрузка в целом является столбцом таблицы и находится в базе данных.
поэтому мы можем использовать update для изменения всего значения всего столбца, а не для изменения его части…
Поэтому, если мы хотим обновить или удалить что-то из колонки jsonb (например, полезную нагрузку здесь), мы должны заменить всю полезную нагрузку на новую и сохранить ее в базе данных, и это то, что мы обнаружим сейчас …
Обновление и вставка ключей в postgres
jsonb_set — это функция, которая позволяет изменить значение определенного ключа и возвращает новую версию jsonb-столбца, которую мы можем использовать в операторе обновления для замены старого значения.
из Postgres
| Функция | Возвращаемый тип | Описание | Пример | Пример Результат |
|---|---|---|---|---|
| jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) | jsonb | Возвращает target с разделом, обозначенным path, замененным на new_value, или с добавленным new_value, если create_missing равен true (по умолчанию true) и элемент, обозначенный path, не существует. Как и в случае с операторами, ориентированными на путь, отрицательные целые числа, которые появляются в path, отсчитываются от конца массивов JSON. | jsonb_set(‘[{«f1″:1,»f2»:null},2,null,3]’, ‘{0,f1}’,'[2,3,4]’, false) | jsonb_set(‘[{«f1″:1,»f2»:null},2]’, ‘{0,f3}’,'[2,3,4]’) |
Сначала, чтобы объяснить jsonb_set, я использую его в операторе SELECT, чтобы увидеть возвращаемое им значение
в массиве [{"f1":1, "f2":null},2] jsonb массив заменяет значение пути {0,f3}, что означает, как мы видели ранее, что значение ключа f3 внутри первого элемента — 0 индекс — {"f1":1, "f2":null}.
в примере ниже мы хотим изменить значение этого f3 на [2,3,4] и здесь create_missing params не передается в функцию, поэтому мы будем использовать значение по умолчанию, которое true означает создать f3 если он не существует и затем вернуть новую версию всего jsonb после обновления.
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
-- jsonb_set
-- ---------------------------------------------
-- [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- (1 row)
но здесь в create_missing мы передаем (false), что означает, что если вы не нашли f3 не создавайте его (только обновление)
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]',false);
-- jsonb_set
-- ----------------------------
-- [{"f1": 1, "f2": null}, 2]
-- (1 row)
здесь мы не нашли f3, поэтому возвращаемое значение будет исходным (при изменении)
Давайте узнаем больше на этих примерах …
Да, больше примеров, как вы знаете…
Если вы следили за этой серией, вы знаете, что я использую много примеров, так как считаю, что это отличный способ открыть и изучить новую информацию.
Здесь мы используем -1 как индекс, который означает последний элемент в массиве (отрицательные целые числа, которые появляются в пути, считаются с конца).
SELECT jsonb_set('[1,2,{"f1":1,"f2":null}]', '{-1,f2}','[2,3,4]',false);
-- jsonb_set
-- ------------------------------------
-- [1, 2, {"f1": 1, "f2": [2, 3, 4]}]
-- (1 row)
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0}','[2,3,4]',false);
-- jsonb_set
-- ----------------
-- [[2, 3, 4], 2]
-- (1 row)
Что если мы хотим изменить значение publisher в нашем примере с книгой … здесь я использую SELECT просто чтобы показать новое значение payload
SELECT jsonb_set(payload, '{publisher}', '"new publisher"') from books WHERE title = 'book1';
-- {"price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
теперь давайте действительно обновим значение publisher в book1, поскольку теперь это новый издатель!
UPDATE books SET payload = jsonb_set(payload, '{publisher}', '"new publisher"') WHERE title = 'book1';
разбирать более сложные примеры с помощью tags.
- Добавьте новые ключевые теги или обновите их, если они уже существуют
UPDATE books SET payload = jsonb_set(payload, '{tags}', '["tag3", "tag4"]') WHERE title = 'book1';
-- {"tags": ["tag3", "tag4"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- Замена второго тега (с индексом 0):
UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
Удаление ключа из JSONB
jsonb_set — действительно отличный инструмент для обновления или добавления новых ключей в jsonb… но как насчет удаления или объединения значений?
К счастью, у нас есть несколько хороших операторов jsonb, которые сделают это за нас
взгляните на них в этой таблице из документации postgres
| Оператор | Тип правого операнда | Описание | Пример |
|---|---|---|---|
| || | jsonb | Конкатенация двух значений jsonb в новое значение jsonb | ‘[«a», «b»]’::jsonb || ‘[«c», «d»]’::jsonb |
| — | текст | Удалить пару ключ/значение или элемент строки из левого операнда. Пары ключ/значение подбираются на основе их ключа. | ‘{«a»: «b»}’::jsonb — ‘a’ |
| — | целое число | Удаляет элемент массива с указанным индексом (неотрицательные целые числа считаются с конца). Выбрасывает ошибку, если контейнер верхнего уровня не является массивом. | ‘[«a», «b»]’::jsonb — 1 |
| #- | text[] | Удаление поля или элемента с указанным путем (для массивов JSON отрицательные целые числа считаются с конца) | ‘[«a», {«b»:1}]’::jsonb #- ‘{1,b}’ |
Как и раньше, я считаю удобным сначала объяснить это с помощью SELECT, чтобы показать конечный результат нашего jsonb
||
две трубы || используют конкатенацию объектов или массивов jsonb.
SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
-- ?column?
-- ----------------------
-- ["a", "b", "c", "d"]
-- (1 row)
не будет удалять дубликаты в массивах jsonb
SELECT '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;
-- ?column?
-- ----------------------
-- ["a", "b", "a", "d"]
-- (1 row)
SELECT '{"a":1, "b":2}'::jsonb || '{"c":3, "d":4}'::jsonb;
-- ?column?
-- ----------------------------------
-- {"a": 1, "b": 2, "c": 3, "d": 4}
-- (1 row)
но в объекте jsonb, как мы показали в первом уроке этой серии, jsonb заменяет дубликаты ключей последним значением
SELECT '{"a":1, "b":2}'::jsonb || '{"a":3, "d":4}'::jsonb;
-- ?column?
-- --------------------------
-- {"a": 3, "b": 2, "d": 4}
-- (1 row)
—
для удаления ключа используйте мин - со строкой ключа в объекте jsonb
SELECT '{"a": "b"}'::jsonb - 'a';
-- ?column?
-- ----------
-- {}
-- (1 row)
это нормально, если ключ не существует, так как ошибка не возникнет … круто и удобно !
SELECT '{"a": 1,"b":2}'::jsonb - 'c';
-- ?column?
-- ------------------
-- {"a": 1, "b": 2}
-- (1 row)
для удаления элемента используйте мин - с номером индекса или строкового элемента в массиве jsonb
SELECT '["a", "b"]'::jsonb - 1;
-- ?column?
-- ----------
-- ["a"]
-- (1 row)
SELECT '["a", "b"]'::jsonb - -1;
-- ?column?
-- ----------
-- ["a"]
-- (1 row)
SELECT '["a", "b"]'::jsonb - 'a';
-- ?column?
-- ----------
-- ["b"]
-- (1 row)
мы не можем удалить из объекта, используя целочисленный индекс, если целочисленный является ключом в jsonb, то он должен быть строковым
SELECT '{"1":1, "2":2}'::jsonb - 1;
ERROR: cannot delete from object using integer index
SELECT '{"1":1, "2":2}'::jsonb - '1';
?column?
----------
{"2": 2}
(1 row)
#-
что если мы хотим удалить поле или элемент с указанным путем … конечно, мы знаем, что для удаления нужно использовать -, а для выбора пути можно использовать #, который указывает, что мы будем использовать путь — как мы видим в #> и #>>…
поэтому давайте объединим функциональность # и -, чтобы получить #-, который удалит поле или элемент с указанным путем
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
-- ?column?
-- -----------
-- ["a", {}]
-- (1 row)
SELECT '{"1": {"b":1,"c":2}}'::jsonb #- '{1,b}';
-- ?column?
-- -----------------
-- {"1": {"c": 2}}
-- (1 row)
как я уже говорил, ничего страшного, если он не найдет элемент по пути, никакие ошибки не будут раздражать вас
SELECT '["a", {"b":1}]'::jsonb #- '{0,b}';
-- ?column?
-- -----------------
-- ["a", {"b": 1}]
-- (1 row)
Давайте поработаем с тегами в книге… на этом примере
это текущее значение полезной нагрузки для одной книги
{"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
и в этих примерах мы будем показывать изменения в нем после каждого оператора обновления
- Удалите последний тег:
UPDATE books SET payload = payload #- '{tags,-1}' WHERE title = 'book1';
-- {"tags": ["tag3"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- Сложное обновление (удаление последнего тега, вставка нового тега, а также изменение имени или его вставка, если его нет):
- удалить последний тег
#- '{tags,-1}' - вставить новый тег
jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true) - изменить имя или вставить его
jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"')
- удалить последний тег
UPDATE books SET payload = jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- больше интересных примеров для практики …
UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
UPDATE books SET payload = payload || '{"a": "apple"}' WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
- Здесь обратите внимание, что я использую
select, а неupdate. Таким образом, исходное значение (которое было получено в последнем операторе обновления) не будет меняться после каждого примера.
SELECT payload - 'a' from books WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
SELECT (payload -> 'tags') - 'tag10' from books WHERE title = 'book1';
-- ["tag5"]
-- tags are "tags": ["tag10", "tag5"] the proives query is select not update
SELECT (payload -> 'tags') - 0 from books WHERE title = 'book1';
-- ["tag5"]
SELECT payload #- '{"tags",0}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
SELECT payload #- '{"authors",0,"name"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
-- no error if key is not found
SELECT payload #- '{"authors",0,"age"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
Фрагменты кода :
все примеры кода в серии jsonb в моем аккаунте GitHub 💻jsonb_posts
Ссылки
https://aaronbos.dev/posts/update-json-postgresql