Уважаемые разработчики, помогите с построением запроса, хотя предполагаю, что в конкретном случае потребуется написать процедуру.
Имеется следующая структура таблицы:
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), сам понимаю. А как правильно организовать цикл по ключам не знаю. Возможно задача решается вообще без цикла? Надеюсь найдутся люди, разбирающиеся в подобных вопросах...
Средний
Средний