mysql query on json data

Question

I have a param field in my mysql table what's contains json data. After encode looks like this:

  'config.enable_comments' => string '1' 
  'metadata.description' => string '' 
  'metadata.keywords' => string '' 
  'metadata.robots' => string '' 
  'metadata.author' => string '' 
  'config.primary_category' => string '157'

how can i make a mysql query depending for example on config.primary_category.

SELECT params
FROM #__zoo_item
WHERE  config.primary_category = ". $id;

thats a bad idea to store it as json. you really should normalize your database. — Daniel A. White
– Daniel A. White, Commented Feb 15, 2012 at 14:05
If it isn't too late to change this to a proper normalization scheme, you absolutely should change it. This is going to cause you lots and lots of headaches... — Michael Berkowski
– Michael Berkowski, Commented Feb 15, 2012 at 14:07
the thing is that this one is a joomla component(purchased) what is storing some params as json and I would like to trigger out some items where config.primary.category is equivalent with id. I'm not a professional maybe I'm doing something wrong. — lgt
– lgt, Commented Feb 15, 2012 at 14:11
Safest thing is to retrieve the json string, json_decode it back to a native PHP structure, and work with that. About all you can inside MySQL is string operations, and you're not VERY careful with those, you'll get garbage for results. — Marc B
– Marc B, Commented Feb 15, 2012 at 14:21

JoRobles · Accepted Answer · 2013-04-10 19:49:03Z

2

And over a year later... I needed to do this kind of query with a project where the database can not be normalized for some reason.

This is one possible solution:

SELECT * 
FROM myTable 
WHERE if(
  instr(myField,'myJsonField'),
  substring(
    substring(myField,instr(myField,'myJsonField')+15),
    1,
    instr(substring(myField,instr(myField,'myJsonField')+15),'\",\"')-1
  ),
  ''
) = 'theJsonValue'

Please note that 'myJsonField' has a length of 11, adding to that the symbols of separation of JSON format is 15.

edited Apr 10, 2013 at 19:49

answered Apr 10, 2013 at 18:17

JoRobles

961 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query on json data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related