3

I have saved the details of the product in JSON format.
I am wondering how to select from a list of values from a "JSON_ENCODED DATA"

For Example I want to select TV with screen_type as LED or LCD.

And the value is saved as

{"screen_size":"22 - 24", "screen_type":"LED", "smart_tv":"no", "3d":"no", "resolution":"HD Ready", "hdmi":"1", "usb":"1"}

It is possible to do with UNION query like:

SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LED"%'  
UNION  
SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LCD"%'

But the query will be too lengthy if there are lot of choices. Is there are any solution in a simpler way.

  • If you need to search on the data, do not store in JSON format. Add proper columns in the table for the attributes that you want to search one (screen_type, screen_size, resolution, etc...) – ypercubeᵀᴹ Aug 2 '14 at 15:13
  • but @ypercube I don't have a predefined set of attributes. The attributes are selected dynamically, based on the selected category. – Eugine Joseph Aug 5 '14 at 9:26
  • It won't be easy then and the queries will not be efficient at all. I suggest you look into Postgres with (hstore or JSON). – ypercubeᵀᴹ Aug 5 '14 at 9:30
3

I got a solution:

  1. Single check of value in JSON array

    SELECT * FROM `nw_object_detail` 
    WHERE ((`details` like '%"screen_type":"%LED%"%'))
    
  2. One value from multiple possibilities

    SELECT * FROM `nw_object_detail`
    WHERE ((`details` like '%"screen_type":[%"%LCD%"%]%') 
    

PHP Solution

if(count($options)>1): 
    $condition = '(';
    $count = 1;
    foreach( $options as $option ): 
        $condition .= '(`details` like \'%"'.$key.'":[%"%'.$option.'%"%]%\')';
        if(count($options)!=$count): 
            $condition .= ' OR ';
        endif;
    endforeach;
$condition .= ')';    
  • Why did you change the LIKE '%"screen_type":"LED"%' to LIKE '%"screen_type":%"%LED%"%'? This may result in false positives. – ypercubeᵀᴹ Aug 5 '14 at 9:32
  • you are right @ypercube. it points to false positivies. When converting to json, the data with one value are saved in format "key":"value" and the array as "key":["value1","value2"] – Eugine Joseph Aug 5 '14 at 11:16
  • Make sure to safely escape those values to prevent SQL injection! – Nepoxx Sep 24 '15 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.