243 posts
  • Has been part of the Envato Community for over 5 years
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
  • Located in Europe
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
  • Has been part of the Envato Community for over 5 years
  • Has sold $1,000+ on Envato Market
  • Had an item featured in an Envato Bundle
  • Has collected 1+ items on Envato Market
+2 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.

766 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $250,000+ on Envato Market
  • Sells items exclusively on Envato Market
  • Made it to the Authors' Hall of Fame
+6 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
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
  • Has collected 1+ items on Envato Market
  • Located in Europe
+3 more
webarto says

Try…

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