As far as my requirement concerns,I need to update a jsonb column (Not in a format of flat json but as a nested json) inside postgres.
I tried to convert the nested json into flat json using a function (array_data_to_json) and then I tried to use jsonb_extract_path in-order to get the exact path of that required node.
My query is like this,
UPDATE public.employee
SET emp_json = jsonb_set(emp_json, jsonb_extract_path(array_data_to_json(emp_json), 'promotion'), '"No"')
WHERE id = 1234;
But Im getting an error - ERROR: function jsonb_set(jsonb, jsonb, unknown) does not exist
My actual nested json is like this,
[
"promotion": {
"name": "Promotion",
"value": "Yes",
"inputType": "Text",
"displayProperty": {
"validation": "",
"displayName": "Promotion",
"defaultValues": ""
}
}]
And after using my custom function array_data_to_json(emp_json), its output will be like this,
{ "promotion": "Yes"}
Then, I need to update this promotion as No