243 posts
  • 6 Years of Membership
  • Affiliate Level 1
  • Author Level 3
  • Weekly Top Seller
creativestuff
says
hi, I have a database having a filed “ex” containing json string: {"var1":"val1","var2":"val2"...} I want to make a filter for this filed and was thinking to query it with SELECT id FROM.... WHERE `ex` LIKE '%"variable":"%whatiamlookingfor%"%' this is not good since it will return this too {"variable":"blabla","other varible":"not whatiamlookingfor"}

Any idea? or a regex ? (I’m not good with regex)

320 posts
  • 6 Years of Membership
  • Author Level 3
  • Bundle Boss
  • Collector Level 1
+3 more
jwmcpeak
says

I’ll preface this by saying I’m not a database expert; in fact far from it. With that out of the way…

My general rule of thumb: if I have to query it, I create a table for it. So, I would create a table to store the individual pieces of your JSON (ie: columns are keys). You’re storing a data format (JSON) in another data format (a table), and you need to query the JSON in an environment that has no JSON parsing (resulting in you using LIKE ). LIKE is process intensive due to its nature (searching the contents of a field), and as your table grows, the database server will have to spend more time performing the LIKE query—eventually resulting in a timeout for your query.

1001 posts
  • Elite Author
  • Author Level 9
  • Top Monthly Author
  • Trendsetter
+9 more
LCweb
says

Hi, normally if I used JSON to store data into the DB I use a query like this:

SELECT ... FROM ... WHERE 'ex' LIKE '%"WHATYOURELOOKINGFOR"%'

It works perfectly even in tables with thousands of rows, but I search the exact string.

In other cases I agree with jwmcpeak: using a regex with JSON could be a very slow solution. I suggest to use a dedicated DB table.

18 posts
  • Affiliate Level 1
  • Author Level 3
  • Collector Level 1
  • Helpful Hacker
+3 more
webarto
says

Try…

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"]*)key_word([^"]*)"';
by
by
by
by
by
by