Mysql Like(or regex) field containing JSON variable

#1

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)

#2

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.

#3

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.

#4

Try…

SELECT id FROM table_name WHERE field_name REGEXP ‘“key_name”:"([^"])key_word([^"])"’;