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.
2
Answers
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:
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.
1st solution, simple but to be used carefully
You convert your
json
data intotext
and you use thereplace
function :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 :Then you get your result with the following query :
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 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 in dbfiddle