CODEWITHSUNDEEP
regexjsonpostgresql-9.3regexp-replace
Oleksandr Diudiun
Oleksandr Diudiun

Reputation: 4354

How to do regexp for add new json element in Postgres

What I have and what I know:

What I need:

What I was try:

SELECT regexp_replace('{"home":["1","2","5"], "work":["15","16","19"]}', '(.*)("home":\[)(("[0-9]*",)*("[0-9]*")*)(\])(.*)', '\1\2\3,"11"\6\7', 'g');

What I don't know: - What if place will be "home":[] and I must put "11" without ","! how?

Upvotes: 0

Views: 2076

Answers (2)

jpmc26
jpmc26

Reputation: 29934

Don't use regex for processing JSON. It's just a bad idea. Just like HTML, JSON is not a regular language. JSON should be parsed for processing.

I worked at this in pure SQL a good while, and it got ugly. Then I installed PL/v8 and it took me about 10 minutes. Install that (installation will vary by platform), and then run:

CREATE EXTENSION plv8;

Then it's easy. I just created a function:

CREATE OR REPLACE FUNCTION add_to_keyed_list(obj JSON, obj_key TEXT, new_value JSON)
  RETURNS JSON
  LANGUAGE plv8
  AS $$
    obj[obj_key].push(new_value);
    return JSON.stringify(obj);
  $$
;

Yes, that is JavaScript code inside your database.

Ran a query:

SELECT add_to_keyed_list('{"home":["1","2","5"], "work":["15","16","19"]}'::JSON, 'home', to_json('11'::TEXT));

And got back exactly what you wanted:

{"home":["1","2","5","11"],"work":["15","16","19"]}

I think it's fair to say that PG doesn't really have good tools for modifying JSON right now, but regex certainly isn't a good tool for it, either. PL/v8 is absolutely a legitimate choice for JSON processing.

Upvotes: 1

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89584

I'm not a postgresql expert, but you can perhaps try this trick:

SELECT regexp_replace('{"home":["1","2","5"], "work":["15","16","19"]}' || ',"11"', '("home":\[)(?:(].*)(.{4}$)|([^]]+)(.*)(.{5}$))|.{5}$', '\1\3\4\2\6\5', 'g');

The idea is to concatenate the original string with ,"11" before. When the array is empty, the group 3 captures only "11", but when the array isn't empty the group 6 captures ,"11".

.{5}$ will match ,"11" at the end of the string and since all capture groups are empty, it will be deleted by the replacement string.

Obviously, the quantifiers must be adapted to the length of the string you want to add. So if you want to add 13240 the pattern will be:

("home":\[)(?:(].*)(.{7}$)|([^]]+)(.*)(.{8}$))|.{8}$

You can see an example here.

Note: you can do the same without explicit quantifiers:

("home":\[)(?:(].*)("[^"]*"$)|([^]]+)(.*)(,"[^"]*"$))|,"[^"]*"$

Upvotes: 1

Related Questions