Как выполнять операции обновления столбцов типа 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;

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

Ответы

Ответ 1

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

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

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

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

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

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

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

Этот ответ привлек столько же отрицательных голосов, сколько и все мои другие ответы на SO вместе. Людям, похоже, идея не нравится: нормализованный дизайн лучше для нединамических данных. Этот отличный пост в блоге Крейга Рингера объясняет более подробно:

Ответ 2

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

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

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

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

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

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 заменяется окончательной версией.

Ответ 3

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

Ответ 4

Для тех, кто сталкивается с этой проблемой и хочет очень быстро исправить (и застрял на 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;

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

Есть два важных предостережения

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

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

Ответ 5

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

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

Ответ 6

Я написал для себя небольшую функцию, которая рекурсивно работает в 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)

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

Ответ 7

Может быть: UPDATE test SET data = ' "my-other-name" ':: json WHERE id = 1;

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

Ответ 9

обновите атрибут name:

UPDATE test SET data=data||'{"name":"my-other-name"}' WHERE id = 1;

и если вы хотите удалить, например, атрибуты 'name' и 'tags':

UPDATE test SET data=data-'{"name","tags"}'::text[] WHERE id = 1;