0

Regexs baffle me. I have a situation where I have JSONs stored in a db. I need to go into the JSON string, find a part of it, and increment it.

In this case, the phrase I'm searching for is "selectedLib":"x" where x is always a number (but a number that has, in this case, been wrapped in quotes and so is technically a string).

So, "selectedLib":"1", needs to become "selectedLib":"2", "selectedLib":"23" needs to become "selectedLib":"24", and so on.

This is a real head-scratcher...

Benjamin Allison
  • 2,134
  • 3
  • 30
  • 55
  • MySQL can only use regexp to match strings, it doesn't have regexp replace. – Barmar Sep 15 '14 at 21:44
  • 2
    I guess this should teach you not to store JSON in the database if you need to use the value. You should normalize your schema and put the data in a real table. – Barmar Sep 15 '14 at 21:45
  • I know I know. The problem is that these JSONs are essentially a document format that is used by a JS front-end (Knockout). Normalizing something like that will really kill performance and add bloat. – Benjamin Allison Sep 15 '14 at 21:49
  • 1
    In which case, you'll most likely need to pull the data out, increment the value, and then re-store – M Sost Sep 15 '14 at 21:51
  • "add bloat", don't think you can call something bloat if it makes your database design much easier to manipulate. "kill performance", I think you are killing performance with JSON now that you will need to pull all data, increment it, and then update all data (rather than just `UPDATE table SET column+=1`). – Sam Sep 15 '14 at 21:53
  • It's a one time operation because some other db schema has changed. Something that happens extremely seldomly. – Benjamin Allison Sep 15 '14 at 21:55
  • You have to use regular string functions: `LOCATE`, `SUBSTR`, `CONCAT`. – Barmar Sep 15 '14 at 21:55
  • See this: https://github.com/hholzgra/mysql-udf-regexp – Barmar Sep 15 '14 at 21:56
  • You can store JSON in the database, but don't expect SQL to be a convenient language to manipulate it. – Bill Karwin Sep 15 '14 at 22:01

0 Answers0