243 posts
  • Europe
  • Has been a member for 5-6 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
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
  • Author had a File in an Envato Bundle
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 5-6 years
  • Sold between 1 000 and 5 000 dollars
  • United States
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.

672 posts
  • Elite Author
  • Sold between 250 000 and 1 000 000 dollars
  • Most Wanted Bounty Winner
  • Bought between 10 and 49 items
  • Exclusive Author
  • Referred between 100 and 199 users
  • Has been a member for 3-4 years
+1 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
  • Europe
  • Helpful Hacker
  • Sold between 1 000 and 5 000 dollars
  • Has been a member for 3-4 years
  • Exclusive Author
  • Bought between 1 and 9 items
  • Referred between 1 and 9 users
webarto says

Try…

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