I have a json column with this format and How to modify element from postgres jsonb by key?

{
  "Content": {
    "CustomerInfo": {
    },
    "Identifier": null,
    "Operating": {
      "Identifier": "ABC-8585",
      "TypeIdentifier": "VAL_OP",
      "SaleIdentifier": "01000042"
    },
  },
}

How to remove "ABC-" from Identifier key?

Result In:

{
  "Content": {
    "CustomerInfo": {
    },
    "Identifier": null,
    "Operating": {
      "Identifier": "8585",
      "TypeIdentifier": "VAL_OP",
      "SaleIdentifier": "01000042"
    },
  },
}

Here is the query I apply to update a json value but it does not work.

update "tbleName" set "columnName" = replace('Content.Operating.Identifier','ABC-','')::jsonb ? 'Content.Operating.Identifier'

CodePudding user response：

You can achieve this by using a combination of jsonb_set, regular JSON reading operators, and replace:

UPDATE "tbleName"
set "columnName" = jsonb_set(
  "columnName",
  '{Content,Operating,Identifier}', 
  replace(("columnName"::jsonb -> 'Content' -> 'Operating' -> 'Identifier')::text, 'ABC-', '')::jsonb
);

Here you compute the column's new value in the following way:

1. read the value of the desired nested JSON key
2. do the replacement
3. write back the result to the same nested JSON key in the original JSON structure