How to do regexp for add new json element in Postgres

Question

What I have and what I know:

json array like {"home":["1","2","5"], "work":["15","16","19"]}
new integer element ("11" for example)
place for new element "home" or "work" ("home" for example)

What I need:

Add this element to end of json array in right place
Do it with reqular expression
Do it in postgreSQL with regexp_replace
Result must be like this: {"home":["1","2","5","11"], "work":["15","16","19"]}

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?

Casimir et Hippolyte · Accepted Answer · 2015-02-14 00:08:29Z

1

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":\[)(?:(].*)("[^"]*"$)|([^]]+)(.*)(,"[^"]*"$))|,"[^"]*"$

edited Feb 14, 2015 at 0:08

answered Feb 13, 2015 at 23:18

Casimir et Hippolyte

90k5 gold badges102 silver badges131 bronze badges

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

4 Comments

Oleksandr Diudiun Over a year ago

Thanks for reply! I tried you code and have result without "11" in "home" section: {"home":["1","2","5"], "work":["15","16","19"]}.

Oleksandr Diudiun Over a year ago

Looks very good! but postgres return original string like before || ',"11"'

Oleksandr Diudiun Over a year ago

Why example on regex101.com/r/aP6zY7/1 work like a charm and postgres not :(

Casimir et Hippolyte Over a year ago

@noonehos: perhaps it is not possible to use capture groups in lookaheads, so I have build a version without them.

jpmc26 · Accepted Answer · 2015-02-14 01:00:16Z

1

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.

edited Feb 14, 2015 at 1:00

answered Feb 14, 2015 at 0:19

jpmc26

30.3k14 gold badges100 silver badges152 bronze badges

2 Comments

Oleksandr Diudiun Over a year ago

Will try today. can I use it for 1000 lines of update or 100 000? How slow it will work? and what be faster JS function or REGEXP? Thanks!

jpmc26 Over a year ago

@noonehos You will have to benchmark. There's no way of knowing for sure until you actually test. I can say that a regular expression will be harder to write, harder to understand, and more prone to breaking (either when you try to change it or on edge cases you didn't consider). As long as the performance is acceptable for your use case, those (which fall into the categories of "correctness" and "maintainability") trump performance for me. Performance is always the last thing to be considered. I would expect they have similar performance, but that's just a guess.

Collectives™ on Stack Overflow

How to do regexp for add new json element in Postgres

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related