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?

This is not a valid JSON.

klin
– klin

2017-12-08 19:24:36 +00:00
Commented Dec 8, 2017 at 19:24 — klin
– klin, Commented Dec 8, 2017 at 19:24
@klin, whoops, I made the example json valid.

crowhoplaminar
– crowhoplaminar

2017-12-08 19:37:06 +00:00
Commented Dec 8, 2017 at 19:37 — crowhoplaminar
– crowhoplaminar, Commented Dec 8, 2017 at 19:37

klin · Accepted Answer · 2017-12-08 19:42:47Z

36

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)

answered Dec 8, 2017 at 19:42

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kleinohad · Accepted Answer · 2022-11-23 11:04:58Z

22

Use update:

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

edited Nov 23, 2022 at 11:04

kleinohad

5,9202 gold badges29 silver badges35 bronze badges

answered Feb 22, 2022 at 13:09

Sajad Abdollahi

2212 silver badges4 bronze badges

1 Comment

Tyler2P Over a year ago

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.

Collectives™ on Stack Overflow

Globally replace in Postgres JSONB field

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related