CODEWITHSUNDEEP
phpmysqljson
lgt
lgt

Reputation: 1034

mysql query on json data

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;

Upvotes: 1

Views: 683

Answers (1)

JoRobles
JoRobles

Reputation: 96

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.

Upvotes: 2

Related Questions