How to use jsonb_extract_path inside jsonb_set

Viewed 32

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

0 Answers
Related