Update value inside of nested json array-CodePudding

I have JSON stored in a table. The JSON is nested and has the following structure

[
  {
    "name": "abc",
    "ques": [
      {
        "qId": 100
      },
      {
        "qId": 200
      }
    ]
  },{
    "name": "xyz",
    "ques": [
      {
        "qId": 100
      },
      {
        "qId": 300
      }
    ]
  }
]

Update TABLE_NAME 
  set COLUMN_NAME = jsonb_set(COLUMN_NAME, '{ques,qId}', '101') 
WHERE COLUMN_NAME->>'qId'=100

I am trying to update qId value from JSON. If qId is 100, I want to update it to 101.

CodePudding user response：

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.

CodePudding user response：

1st solution, simple but to be used carefully

You convert your json data into text and you use the replace 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 own aggregate based on the jsonb_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 that jsonb_array_elements can be used in place of jsonb_path_query, but you will get an error with jsonb_array_elements when the jsonb data is not an array, whereas you won't get any error with jsonb_path_query in lax mode which is the default mode.

Full test results in dbfiddle