1

I've been trying with no success to find the right regexp to find and in inside a json array: So far I've tried: '[\\[,]1[\\],]' that I'm gessing will find the rows that contain the id( the hard coded 1 ) that is sorrounded either by '[' or ',' in the left side or by ']' or ',' in the right side. Lets take these sample arrays as an example: [1,2,4]
[5,1,4]
[1]
[5,2,4,1]
[2,4]

Thanks a lot and sorry about my English ;)

Hector Lugo
  • 23
  • 4
  • what do you mean by parsing? – Hector Lugo Mar 18 '15 at 15:59
  • 1
    See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/q/3653462). – eggyal Mar 18 '15 at 15:59
  • 1
    why do you want to use regexp for that in the first place? If you store json files in the db, then at least also create a column with your ID (the one that is also in the json), so you can easily search for it. – Alex Mar 18 '15 at 16:03
  • thanks for the note but it can't be that hard to just check for such a simple pattern, what I'm doing wrong? – Hector Lugo Mar 18 '15 at 16:04
  • @Ixer the reason is simple, I need performance so I simply store the json array coming from the client. The json is an array of allowed ids so I need to check that the id o the current user is in the array – Hector Lugo Mar 18 '15 at 16:06
  • Well would't you check if the current user's id in the array before serializing it and inserting it into MySQL? The comment above by @lxer is a good one in that, if you want to access the information in an easily indexable way, you need to create appropriate columns (or consider NoSQL storage that can allow for such indexing). Doing a REGEXP operatoin will not be able to use an index anyway, so you are not optimizing anything. – Mike Brant Mar 18 '15 at 16:11
  • Just don't store JSON in MySQL! –  Mar 18 '15 at 16:28
  • @IkoTikashi , sorry, but contrary to popular believe, storing Json (or XML) is just fine. Just be sure you don't need to search inside the json often. For example, storing some simple user preferences in a json is fine, and easy to use in your web-app since you can pass it directly to javascript. – Alex Mar 18 '15 at 16:34

1 Answers1

0

first escape the [ , then a possible digit or , , then your id (1)

'\\[[0-9,]*[1][0-9,]*\\]'

However, regexp (in a text / blob field) is slow and leads to performance problems when your database gets bigger. Better also store your ID (or any field that you often need to search) in an extra column.

Alex
  • 5,759
  • 1
  • 32
  • 47