243 posts
  • Has been part of the Envato Community for over 5 years
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
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.

860 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
  • Helped improve Envato sites by detecting a security issue
+2 more
webarto says

Try…

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

Helpful Information

  • Please read our community guidelines. Self promotion and discussion of piracy is not allowed.
  • Open a support ticket if you would like specific help with your account, deposits or purchases.
  • Item Support by authors is optional and may vary. Please see the Support tab on each item page.

Most of all, enjoy your time here. Thank you for being a valued Envato community member.

Post Reply

Format your entry with some basic HTML. Read the Full Details, or here is a refresher:

<strong></strong> to make things bold
<em></em> to emphasize
<ul><li> or <ol><li> to make lists
<h3> or <h4> to make headings
<pre></pre> for code blocks
<code></code> for a few words of code
<a></a> for links
<img> to paste in an image (it'll need to be hosted somewhere else though)
<blockquote></blockquote> to quote somebody

:grin: :shocked: :cry: Complete List of Smiley Codes

by
by
by
by
by
by