1st solution, simple but to be used carefully

You convert yourjson data intotext and you use thereplace function :

Update TABLE_NAME   set COLUMN_NAME = replace(COLUMN_NAME :: text,'"qId": 100}', '"qId": 101}') :: jsonb

2nd solution more elegant and more complex

jsonb_set cannot make several replacements in the same jsonb data at the same time. To do so, you need to create your ownaggregate based on thejsonb_set function :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb,  path text[], new_value jsonb) RETURNS jsonb LANGUAGE sql AS $$SELECT jsonb_set(COALESCE(x, y), path, new_value) ; $$ ;CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, path text[], new_value jsonb)( stype = jsonb, sfunc = jsonb_set);

Then you get your result with the following query :

UPDATE TABLE_NAME    SET COLUMN_NAME =( SELECT jsonb_set_agg(COLUMN_NAME :: jsonb, array[(a.id - 1) :: text, 'ques', (b.id - 1) :: text], jsonb_build_object('qId', 101))    FROM jsonb_path_query(COLUMN_NAME :: jsonb, '$[*]') WITH ORDINALITY AS a(content, id)   CROSS JOIN LATERAL jsonb_path_query(a.content->'ques', '$[*]') WITH ORDINALITY AS b(content, id)   WHERE (b.content)->'qId' = to_jsonb(100))

Note that this query is not universal, and it must breakdown the jsonb data according to its structure.

Note thatjsonb_array_elements can be used in place ofjsonb_path_query, but you will get an error withjsonb_array_elements when thejsonb data is not an array, whereas you won't get any error withjsonb_path_query in lax mode which is the default mode.

Full test results indbfiddle

You must specify the whole path to the value.

In this case your json is an array so you need to address which element of this array your are trying to modify.

A direct approach (over your example) would be:

jsonb_set(    jsonb_set(        COLUMN_NAME        , '{0,ques,qId}'        , '101'    )    , '{1,ques,qId}'    , '101')

Of course, if you want to modify every element of different arrays of different lengths you would need to elaborate this approach disassembling the array to modify every contained element.

• json
• postgresql