Search and replace key's value in nested JSON

Question

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'

Does this answer your question? [Globally replace in Postgres JSONB field](https://stackoverflow.com/questions/47720767/globally-replace-in-postgres-jsonb-field) — user3738870, Nov 15 '22 at 10:48
@user3738870 I need to update the value based on key, not globally. — Ashwini GUPTA, Nov 15 '22 at 10:51
How about this one? https://stackoverflow.com/questions/64567543/how-to-update-value-in-nested-json-postgres — user3738870, Nov 15 '22 at 10:56
@user3738870 it replaces the whole value but I wanted to cut some values like the old value "ABC-8585" and the new value should be like "8585". — Ashwini GUPTA, Nov 15 '22 at 11:00

score 1 · Accepted Answer · edited Nov 15 '22 at 14:35

1

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

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

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

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

edited Nov 15 '22 at 14:35

Ashwini GUPTA

27
6

answered Nov 15 '22 at 11:05

user3738870

1,415
2
12
24

When I execute your query, it gives the following ERROR: operator does not exist: text -> unknown You might need to add explicit type casts. – Ashwini GUPTA Nov 15 '22 at 11:16
Have you replaced "columnName" with the actual name of your column? – user3738870 Nov 15 '22 at 11:24
Yes, I have updated – Ashwini GUPTA Nov 15 '22 at 11:31
What if you explicitly cast your column to `jsonb`? Something like `"columnName"::jsonb` before the first `->`? – user3738870 Nov 15 '22 at 11:40
1

update this code "UPDATE "tableName" set "columeName" = jsonb_set("columeName"::jsonb,'{Content,Operating,Identifier}', replace(("columeName"::jsonb -> 'Content' -> 'Operating' -> 'Identifier')::text, 'ABC-', '')::jsonb);" – Ashwini GUPTA Nov 15 '22 at 13:29

Search and replace key's value in nested JSON

1 Answers1

Linked