Replace part of json (saved as varchar) using regex-CodePudding

I am trying to replace some content inside JSON saved as VARCHAR in Postgres DB. I am using REGEX_REPLACE function for this. However, in some cases, it ignores my curly braces and produces invalid JSON.

CREATE TABLE expressions (
    id BIGINT,
    expression VARCHAR(512)
);

INSERT INTO expressions VALUES
    (1, '{params:[{"value":"test"}]}'),
    (1, '{params:[{"value":"test1"},{"value":"test2"}]}');
    
UPDATE expressions SET "expression" = REGEXP_REPLACE("expression",
      '"value":"(.*)"','"value":{"value": "\1"}', 'g')
WHERE true;

SELECT * FROM expressions;

The first row produces correct JSON but the second one is missing one closing curcly bracket and produces this: {params:[{"value":{"value": "test1"},{"value":"test2"}}]}.

Does anyone know what is wrong with my expression?

EDIT: I am trying to replace the following: {"param": [{"value":"x"}]} -> {"param": [{"value":{value:"x"}}]}

Basically, nest the value string into nested json with same structure. This needs to work for multiple values in the column so {params:[{"value":"test1"},{"value":"test2"}]} should produce {params:[{"value":{"value":"test1"}},{"value":{"value":"test2"}}]}

CodePudding user response：

Remember that by default .* will match greedily - so in the second case, your second match will be the entire string up through the final " character. Try

UPDATE expressions SET "expression" = REGEXP_REPLACE("expression",
      '"value":"(.*?)"','"value":{"value": "\1"}', 'g')

The question mark will make the match 'lazy', so it will stop trying to match after encountering the first ". The g flag will still ensure that both matches are found.