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?

Answer 1

Use update:

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