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")
You can use:
regexp_replace(text1, '("test":\s*")(([^"]|\\")*)(")', '\1' || 'other value' || '\4' , 'g')
But, warning! The regexp replaces inner ocurences of "test": value.
Notes:
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