Как выполнять операции обновления столбцов типа JSONB в Postgres 9.4

Просматривая документацию по типу данных Postgres 9.4 JSONB, мне не сразу становится ясно, как делать обновления в столбцах JSONB.

Документация для типов и функций JSONB:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

В качестве примера у меня есть эта основная структура таблицы:

CREATE TABLE test(id serial, data jsonb); 

Вставка проста, как в:

 INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}'); 

Теперь, как бы я обновил столбец «данные»? Это недопустимый синтаксис:

 UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1; 

Является ли это документированным где-то очевидным, что я пропустил? Благодарю.

В идеале вы не используете JSON-документы для данных, которые хотите управлять внутри реляционной базы данных. Вместо этого используйте нормализованный реляционный дизайн .

JSON в первую очередь предназначен для хранения целых документов, которые не нужно манипулировать внутри РСУБД. Связанный:

  • JSONB с индексацией против hstore

Обновление строки в Postgres всегда записывает новую версию всей строки. Это основной принцип модели MVCC Postgres . С точки зрения производительности, вряд ли имеет значение, измените ли вы один fragment данных внутри объекта JSON или все его: должна быть записана новая версия строки.

Таким образом, совет в руководстве :

Данные JSON подчиняются тем же соображениям контроля параллелизма, что и любой другой тип данных при хранении в таблице. Несмотря на то, что хранение больших документов практически осуществимо, имейте в виду, что любое обновление получает блокировку на уровне строк для всей строки. Рассмотрите возможность ограничения JSON-документов на управляемый размер, чтобы уменьшить блокировку между обновлениями транзакций. В идеале, документы JSON должны представлять собой атомную датуту, которую диктует деловые правила, не могут быть разумно дополнительно разделены на более мелкие данные, которые могут быть изменены независимо.

Суть его: изменить что-либо внутри объекта JSON, вы должны назначить измененный объект столбцу. Postgres предоставляет ограниченные средства для создания и обработки данных json в дополнение к своим возможностям хранения. Арсенал инструментов значительно вырос с каждым новым выпуском с версии 9.2. Но главное остается: вам всегда нужно назначить полный измененный объект для столбца, и Postgres всегда записывает новую версию строки для любого обновления.

Некоторые методы работы с инструментами Postgres 9.3 или новее:

  • Как изменить поля внутри нового типа данных PostgreSQL JSON?

Если вы можете обновить до Postgresql 9.5, команда jsonb_set будет доступна, как упомянуты другие.

В каждом из следующих операторов SQL я опустил предложение where для краткости; очевидно, вы хотели бы добавить это обратно.

Обновить имя:

 UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"'); 

Замените tags (в отличие от добавления или удаления тегов):

 UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]'); 

Замена второго тега (0-индекс):

 UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"'); 

Добавьте тег ( это будет работать до тех пор, пока будет меньше 999 тегов, изменение аргумента с 999 до 1000 или выше приведет к ошибке) . Это больше не похоже на Postgres 9.5.3, можно использовать гораздо больший индекс) :

 UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true); 

Удалить последний тег:

 UPDATE test SET data = data #- '{tags,-1}' 

Комплексное обновление (удалите последний тег, вставьте новый тег и измените имя):

 UPDATE test SET data = jsonb_set( jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), '{name}', '"my-other-name"'); 

Важно отметить, что в каждом из этих примеров вы фактически не обновляете одно поле данных JSON. Вместо этого вы создаете временную измененную версию данных и назначаете эту модифицированную версию обратно в столбец. На практике результат должен быть одним и тем же, но с учетом этого следует сделать сложные обновления, как и последний пример, более понятными.

В сложном примере есть три преобразования и три временных версии: во-первых, последний тег удален. Затем эта версия преобразуется путем добавления нового тега. Затем вторая версия преобразуется путем изменения поля name . Значение в столбце data заменяется окончательной версией.

Это приходит в 9.5 в виде jsonb_set от Andrew Dunstan на основе существующего расширения jsonbx, которое работает с 9.4

Этот вопрос задавался в контексте postgres 9.4, однако новые зрители, приходящие на этот вопрос, должны знать, что в postgres 9.5 под-документы Create / Update / Delete в полях JSONB поддерживаются базой данных без необходимости расширения функции.

См .: Операторы и функции, модифицирующие JSONB

Для тех, кто сталкивается с этой проблемой и хочет очень быстро исправить (и застрял на 9.4.5 или ранее), вот что я сделал:

Создание тестового стола

 CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}'); 

Обновить заявление для изменения имени свойства jsonb

 UPDATE test SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb WHERE id = 1; 

В конечном счете, принятый ответ правилен тем, что вы не можете модифицировать отдельный fragment jsonb-объекта (в 9.4.5 или ранее); однако вы можете передать объект jsonb в строку (:: TEXT), а затем манипулировать строкой и отбрасывать объект jsonb (:: jsonb).

Существует два важных оговорки

  1. это заменит все свойства, называемые «имя» в json (в случае, если у вас есть несколько свойств с тем же именем)
  2. это не так эффективно, как jsonb_set, если вы используете 9.5

С учетом сказанного я столкнулся с ситуацией, когда мне пришлось обновить схему для контента в объектах jsonb, и это был самый простой способ выполнить именно то, что спрашивал оригинальный плакат.

Я написал для себя небольшую функцию, которая рекурсивно работает в Postgres 9.4. У меня была такая же проблема (хорошо, что они решили эту головную боль в Postgres 9.5). В любом случае, здесь функция (я надеюсь, что она хорошо работает для вас):

 CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB) RETURNS JSONB AS $$ DECLARE result JSONB; v RECORD; BEGIN IF jsonb_typeof(val2) = 'null' THEN RETURN val1; END IF; result = val1; FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP IF jsonb_typeof(val2->v.key) = 'object' THEN result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key)); ELSE result = result || jsonb_build_object(v.key, v.value); END IF; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql; 

Вот пример использования:

 select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb); jsonb_update --------------------------------------------------------------------- {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5} (1 row) 

Как вы можете видеть, он анализирует глубину и обновляет / добавляет значения там, где это необходимо.

Возможно: UPDATE test SET data = ‘”my-other-name”‘ :: json WHERE id = 1;

Он работал с моим делом, где данные являются json-типом

Matheus de Oliveira создал удобные функции для операций JSON CRUD в postgresql. Их можно импортировать с помощью директивы \ i. Обратите внимание на jsonb fork функций, если jsonb, если ваш тип данных.

9.3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282

  • Количество строк, затронутых UPDATE в PL / SQL
  • Код ошибки MySQL: 1175 во время UPDATE в MySQL Workbench
  • UPDATE несколько таблиц в MySQL с использованием LEFT JOIN
  • Interesting Posts

    Как получить член, к которому был применен мой пользовательский атрибут?

    Как Apple находит даты, время и адреса в электронных письмах?

    Как перебирать объект JavaScript?

    Как удалить или деактивировать мою лицензию на Windows 7 на одном компьютере и использовать ее на другом

    атрибут svg viewBox

    Почему SYSTEM продолжает блокировать исполняемые файлы после выхода приложения?

    Возможно ли вернуть ссылку на переменную в C #?

    как получить путь к файлу с SD-карты в android

    Есть ли способ установить текущее местоположение вручную в Windows 8?

    Android M – проверить разрешение во время выполнения – как определить, проверен ли пользователь «Никогда не спрашивать снова»?

    Как выводить результаты запросов MySQL в формате CSV?

    как использовать okhttp для загрузки файла?

    Почему в IEnumerable нет метода расширения ForEach?

    Как получить информацию о видеофайлах из командной строки под Linux

    Анализ ежедневных / недельных данных с использованием ts в R

    Давайте будем гением компьютера.