How to update jsonb field?

Question

SQL

Dear developers, please help with building a query, although I assume that in a particular case it will be necessary to write a procedure.
There is the following table structure:

CREATE TABLE public.rules (
    id integer NOT NULL,
    user_id integer NOT NULL,
    tags jsonb,
    query jsonb
);

You need to update the tags field with the following data:

{
  "{1}": {
    "mark": "some_mark_word",
     "stat": 11844
  }, 
  "{27}": {
    "mark": "another_mark_word",
    "stat": 7911
  },
   "{9}": {
    "mark": "again_mark_word", 
    "stat": 10503
  }
}

So, you need to change the stat field in the tags data structure. That is, by user_id, change the tags field by setting the nested stat field to zero. It must be taken into account that the next point is that the number of fields in the json object is arbitrary, the keys do not necessarily go in order.
Tried like this:

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;

Updates only the first field in the tags structure. In principle, it is clear why, at the beginning I create a dataset with a tag field (json object keys) and a number of records equal to the number of fields in tags, then I say to update the rules by setting the tags field to a specific value, where the tags field contains some key (dataset. tag), of course, the first key was found, the object under this key changed and the entire request worked, but I don’t have any cycle that bypasses all the entries in the dataset.
I started thinking in the direction of bypassing the json object by looping through the keys and successive requests to update the tags field, changing one field under a specific key on each cycle. Something like that:

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;

But my knowledge of plpgsql is not so great that I ran into a banal error ERROR: syntax error at or near "key". I'm not using language constructs correctly, in the line FOR set IN key value FROM jsonb_each (tg_field), I understand it myself. And I don’t know how to properly organize the cycle by keys. Perhaps the task is solved without a cycle at all? I hope there are people who understand these things...

Answer 1

I suspect that with the structure of your json it's easier to make a replacement through conversion to a string

UPDATE rules
SET tags = (regexp_replace(tags::text, '"stat": \d*', '"stat": 0', 'g')::jsonb)
WHERE user_id = 1;