Условно заменить одно значение в строке в столбце jsonb

Самый быстрый и простой способ - изменить столбец как текст:

update table_a
set json_col = replace(json_col::text, '"Berlin"', '"Madrid"')::jsonb
where json_col::text like '%"Berlin"%'
and json_col::text not like '%"Madrid"%'

Это практичный выбор. Вышеупомянутый запрос представляет собой скорее операцию поиска и замены (как в текстовом редакторе), чем изменение атрибутов объектов. Второй вариант более сложный и, конечно, намного дороже. Даже при использовании быстрого движка Javascript (пример ниже) более формальное решение будет во много раз медленнее.

Вы можете попробовать Postgres Javascript:

create extension if not exists plv8;

create or replace function replace_item(data jsonb, from_str text, to_str text)
returns jsonb language plv8 as $$
    var found = 0;
    Object.keys(data).forEach(function(key) {
        if (data[key] == to_str) {
            found = 1;
        }
    })
    if (found == 0) {
        Object.keys(data).forEach(function(key) {
            if (data[key] == from_str) {
                data[key] = to_str;
            }
        })
    }
    return data;
$$;

update table_a
set json_col = replace_item(json_col, 'Berlin', 'Madrid');

Хорошо, я проверил все методы и могу сказать, что вы проделали отличную работу Это мне очень помогло. Позвольте мне поделиться с вами своим мнением.

Способ 1 по предложению Клина. Прекрасно работает и полностью нормально, кроме случаев, когда ключ называется как значение, тогда оба будут заменены ключом и значением. то есть: «Берлин»: «Берлин» становится «Мадрид»: «Мадрид»

Способ 2 с расширением plv8 не работал, потому что мне не хватает контрольного файла мне пришлось установить его, и я просто пропустил этот метод, так что у меня нет отзывы об этом методе. Я получал следующую ошибку: ОШИБКА: не удалось открыть файл управления расширениями "/usr/pgsql-9.5/share/extension/plv8.control": нет такого файла или каталога.

Способ 3 похож на метод 2 с функция jsonb_replace_value отлично работает, заменяет строки, содержащие определенное значение, независимо от ключа. И добавляем условие

WHERE json_col <> jsonb_replace_value(json_col, '"Berlin"', '"Madrid"')

будет избегать пустых обновлений и пропускать строки, которые не нужно обновлять И что-то вроде этого

{"Берлин": "Берлин"} становится {"Берлин": "Мадрид"}, т.е. ключ не трогается, только значение

Метод 4 немного сложнее, он использует Метод 3 и указатели Он работает потрясающе и очень быстро.
И НЕ СУЩЕСТВУЕТ полу-анти-объединение действительно заставляет снова использовать Индекс.
Я был шокирован тем, как быстро он работал !!!

Однако я обнаружил, что все эти методы будут работать, если строка json выглядит так: {"ключ": "значение"} Если мне нужно, например, обновить значение, являющееся объектом json, оно не будет обновляться примерно так:
{"C30":{"id":10044,"value":"Berlin","created_by":"John Doe"}}

БОЛЬШОЕ СПАСИБО вам, ребята. @klin и @ erwin-brandstetter. Это помогло мне узнать что-то новое!

Что делает это трудным, так это то, что вы ищете неизвестный ключи, содержащий интересующий ценности. Инфраструктура Postgres оптимизирована для поиска ключи (или значений массива).

Возможно, это вызвано неоптимальной конструкцией стола. Многие объекты верхнего уровня вашего столбца jsonb могут быть заменены на множество, полностью отбрасывая нерелевантные имена ключей. (Или, может быть, другой массив для имен ключей.) Или, в идеале, для начала с полной нормализованной схемой БД.

Как бы то ни было, вот доказательство концепции, как это может быть быстро и чисто с стандартный Postgres 9.5 или новее в любом случае.

Дополнительная трудность 1: неизвестно, возможны ли повторяющиеся значения. Дополнительная сложность 2: значения частот тоже неизвестны. Дополнительная трудность 3: заменяется только найденное значение первый, и только если целевого значения еще нет. Реализация этого с помощью операций на основе наборов возможна, но громоздка. Вместо этого я написал функцию plpgsql:

CREATE OR REPLACE FUNCTION jsonb_replace_value(_j jsonb, _old jsonb, _new jsonb)
   RETURNS jsonb AS
$func$
DECLARE
   _key text;
   _val jsonb;
BEGIN
   FOR _key, _val IN
      SELECT * FROM jsonb_each(_j)
   LOOP
      IF _val = _old THEN
         RETURN jsonb_set(_j, ARRAY[_key], _new);  -- update 1st key
      END IF;
   END LOOP;

   RETURN _j;  -- nothing found, return original
END
$func$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION jsonb_replace_value(jsonb, jsonb, jsonb) IS '
Replace the first occurrence of _old value with _new.
Call:
    SELECT jsonb_replace_value('{"C1":"Paris","C3":"Berlin","C4":"Berlin"}', '"Berlin"', '"Madrid"')';

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

Теперь это будет просто:

UPDATE table_a
SET    json_col = jsonb_replace_value(json_col, '"Berlin"', '"Madrid"'); -- note jsonb literal syntax!

Если строки все нуждаются в обновлении, мы можем остановиться на этом. Быстрее не станет. (За исключением, возможно, альтернатив, таких как продемонстрировано @klin.)
Если большой процент всех строк нуждается в обновлении, добавьте условие WHERE, чтобы избежать пустых обновлений:

...
WHERE  json_col <> jsonb_replace_value(json_col, '"Berlin"', '"Madrid"');

Видеть:

• Как мне (или я могу) ВЫБРАТЬ DISTINCT для нескольких столбцов?

Обычно, только очень несколько рядов действительно нуждается в обновлении. Тогда итерация по всем строкам с указанным выше запросом стоит дорого. Нам нужен поддержка индекса, чтобы сделать это быстро. Непросто для дела. Я предлагаю индекс выражения на основе функции IMMUTABLE, извлекающей массив значений:

CREATE OR REPLACE FUNCTION jsonb_object_val_arr(jsonb)
   RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (SELECT value FROM jsonb_each_text($1))';

COMMENT ON FUNCTION jsonb_object_val_arr(jsonb) IS '
   Generates text array of values in outermost jsonb object.
   Of limited use if there can be nested objects.';

CREATE INDEX table_a_val_arr_idx ON table_a USING gin (jsonb_object_val_arr(json_col));

Связанные, с дополнительными пояснениями:

• Найти строки, содержащие ключ, в массиве записей JSONB

Запрос, использующий этот индекс:

UPDATE table_a a
SET    json_col = jsonb_replace_value(a.json_col, '"Berlin"', '"Madrid"')
WHERE  jsonb_object_val_arr(json_col) @> '{Berlin}' -- has Berlin, possibly > 1x ..
-- AND    NOT jsonb_object_val_arr(json_col) @> '{Madrid}'
AND    NOT EXISTS (                                         -- .. but not Madrid
   SELECT FROM table_a b
   WHERE  jsonb_object_val_arr(json_col) @> '{Madrid}'  -- note array literal syntax
   AND    b.id = a.id
   );

Полуавтоматическое соединение NOT EXISTS тщательно разработано, чтобы использовать индекс во второй раз.

Прокомментированная более простая альтернатива будет быстрее, если есть несколько строк с 'Berlin' и 'Madrid' - тогда шаг фильтрации в плане запроса будет дешевле.

Должно быть очень быстро.

db <> рабочий пример здесь для Postgres 9.5 демонстрирует все.