1

Is there a regex that could be used in the update statement that would replace a part of a String?

I have a row that has a consistent string across all the rows, so I'm looking for a regex pattern that would look for a specific pattern and replace that? For example, say I have a column row that has the following value:

{
  "test": "Some Value"
  ....
}

What I'm essentially looking for is to match anything with "test": and replace that with whatever I give (for example., "test": "Some Other Value")

joesan
  • 13,963
  • 27
  • 95
  • 232
  • are you using ::json filed? – Vao Tsun Aug 12 '15 at 10:38
  • Please don't use regex to substitute for a json parser. – Craig Ringer Aug 12 '15 at 11:29
  • @craig-ringer Do you know a better way? Please share with us. We are many programers searching it. http://dba.stackexchange.com/questions/54663/how-can-i-update-a-json-element-in-postgresql-json-datatype http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype – Emilio Platzer Aug 13 '15 at 14:57
  • @EmilioPlatzer If it is actually json, use PostgreSQL's json support to unpack the json and iterate over the key/value pairs. If you are on an older version, you can use pl/python or pl/perl and a json library. – Craig Ringer Aug 13 '15 at 23:34
  • But, how to re pack? 9.3 or 9.4 – Emilio Platzer Aug 14 '15 at 02:23

1 Answers1

1

You can use:

regexp_replace(text1, '("test":\s*")(([^"]|\\")*)(")', '\1' || 'other value' || '\4' , 'g')

But, warning! The regexp replaces inner ocurences of "test": value.

Notes:

  1. \" inside strings are Ok!
  2. there is no way of detect internals ocurences
  3. \1 & \4 are captures of the regexp, in this case you could put directly '"test": ' instead of '\1' and '"' instead of '\4'. But \n is more general

A complete example:

drop table if exists example;

create table example(json1 json, text1 text);

insert into example (json1, text1) values (
  '{"in":{"test": "inner"}, "test": "outer", "other": "some"}',
  '{"in":{"test": "inner"}, "test": "outer", "other": "some"}'
);

insert into example (json1, text1) values (
  '{"in":{"test": "inner \"no\""}, "test": "outer \"yes\"", "other": "some"}',
  '{"in":{"test": "inner \"no\""}, "test": "outer \"yes\"", "other": "some"}'
);

select * from example;

select regexp_replace(text1, '("test":\s*")(([^"]|\\")*)(")', '\1' || 'other value' || '\4' , 'g')
  from example;

If you use postgres 9.4 you can use json or jsonb type for a column

Emilio Platzer
  • 2,327
  • 21
  • 29