Globally replace in Postgres JSONB field

Question

I need to globally replace a particular string that occurs multiple places in a nested JSON structure, thats stored as jsonb in a postgres table. For example:

{
  "location": "tmp/config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/config"
  }
}

...should become:

{
  "location": "tmp/new_config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/new_config"
  }
}

I've tried:

UPDATE files SET meta_data = to_json(replace(data::TEXT, 'tmp/config', 'tmp/new_config'));

Unfortunately this results in malformed JSON, with triple escaped quotes.

Any ideas how to do this?

@klin, whoops, I made the example json valid. – crowhoplaminar Dec 08 '17 at 19:37 — crowhoplaminar, Dec 08 '17 at 19:37

score 30 · Accepted Answer · answered Dec 08 '17 at 19:42

Use a simple cast to jsonb instead of to_json(), e.g.:

with files(meta_data) as (
values(
'{
  "location": "tmp/config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/config"
  }
}'::jsonb)
)

select replace(meta_data::text, 'tmp/config', 'tmp/new_config')::jsonb
from files;

                                                replace                                                 
--------------------------------------------------------------------------------------------------------
 {"location": "tmp/new_config", "alternate_location": {"name": "config", "location": "tmp/new_config"}}
(1 row)

score 15 · Answer 2 · edited Nov 23 '22 at 11:04

15

Use update:

UPDATE files
SET meta_data = replace(data::TEXT, 'tmp/config', 'tmp/new_config')::jsonb;

edited Nov 23 '22 at 11:04

kleinohad

5,800
2
26
34

answered Feb 22 '22 at 13:09

Sajad Abdollahi

151
1
4

A good answer will always include an explanation why this would solve the issue, so that the OP and any future readers can learn from it. – Tyler2P Feb 22 '22 at 15:23

Globally replace in Postgres JSONB field

2 Answers2

Linked