alekstar79
@alekstar79

Как обновить jsonb поле?

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

Имеется следующая структура таблицы:
CREATE TABLE public.rules (
    id integer NOT NULL,
    user_id integer NOT NULL,
    tags jsonb,
    query jsonb
);

Необходимо обновить поле tags, со следующими данными:
{
  "{1}": {
    "mark": "some_mark_word",
     "stat": 11844
  }, 
  "{27}": {
    "mark": "another_mark_word",
    "stat": 7911
  },
   "{9}": {
    "mark": "again_mark_word", 
    "stat": 10503
  }
}

Так вот, необходимо изменить поле stat, в структуре данных tags. То есть, по user_id изменить поле tags, выставив вложенное поле stat в ноль. Необходимо учитывать, следующий момент - количество полей в json-объекте любое, ключи не обязательно идут по порядку.

Попробовал так:
WITH dataset AS (SELECT jsonb_object_keys(tags) AS tag FROM rules WHERE user_id = $1)
UPDATE rules
SET tags = (rules.tags || jsonb_build_object(dataset.tag, (rules.tags->tag || '{"stat":0}'::jsonb)))
FROM dataset
WHERE user_id = $1 AND rules.tags ? dataset.tag;

Обновляет только первое поле в структуре tags. В принципе понятно почему, в начале я создаю набор dataset с полем tag (ключами json-объекта) и числом записей равным количеству полей в tags, затем говорю обновить rules, установив поле tags в определенное значение, где поле tags содержит некоторый ключ (dataset.tag), естественно первый ключ нашелся, объект под этим ключом изменился и все запрос отработал, у меня же нет ни какого цикла обходящего все записи в dataset.

Начал думать в сторону обхода циклом по ключам json-объекта и последовательными запросами на обновление поля tags, изменяя на каждом цикле одно поле под конкретным ключом. Что-то вроде этого:

CREATE FUNCTION clear_by_uid(in int) RETURNS BOOLEAN AS $$
DECLARE
    tg_field rules.tags%TYPE;
BEGIN
    SELECT tags INTO tg_field FROM rules AS r WHERE r.user_id = $1;
    FOR set IN key value FROM jsonb_each(tg_field)
    LOOP
        UPDATE rules
        SET tags = (rules.tags || jsonb_build_object(set.key, (set.value || '{"stat":0}'::jsonb)))
        WHERE rules.user_id = $1;
    END LOOP
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Но мои познания в plpgsql не настолько велики, что уперся в банальную ошибку ERROR: syntax error at or near "key". Не правильно я использую конструкции языка, в строке FOR set IN key value FROM jsonb_each(tg_field), сам понимаю. А как правильно организовать цикл по ключам не знаю. Возможно задача решается вообще без цикла? Надеюсь найдутся люди, разбирающиеся в подобных вопросах...
  • Вопрос задан
  • 133 просмотра
Решения вопроса 1
@1001001
Подозреваю, что с структурой вашего json'a проще сделать замену через преобразование к строке
UPDATE rules
SET tags = (regexp_replace(tags::text, '"stat": \d*', '"stat": 0', 'g')::jsonb)
WHERE user_id = 1;
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы