Mysql – thesql 5.7 json filter and features products

featuresjsonMySQL

sorry for my english!
I need use this column(feature) for show features, filters for products and in some language(It depends on the country).
I have products table(innodb) and a column(json) features, the column feature is like this:

{feature:{
        [{"en": "System Operation", 
        "it": "Sistema operativo", 
        "value": "IOS 8"
    }, 
        {
        "en": "Memory Ram", 
        "it": "Memoria Ram", 
        "value": "16 Gb"
    }, 
        {
        "en": "display", 
        "it": "schermo", 
        "value": "5.5\""
    }]}

Here have just 2 language but can have 30 or 100 language.

For show feature i use foraech in php I should optimize… maybe in mysql if is possible.

$language_detect = substr($_SERVER['HTTP_ACCEPT_LANGUAGE'], 0, 2);
if($language_detect == "it") {$lang_det = "it";}
elseif($language_detect == "en") {$lang_det = "en";}
elseif..
elseif...    

foreach($feature->feature as $row) {
      foreach($row as $key => $val){
        if($key == '$lang_det'){
              echo $key->$lang_det .'  ' . $val . ' : ' ; }
        if($key == 'value'){
                echo $val . '<br>' ; }
        }
    }

The really first problem is: how I can make the query like this result?:

        [{
        "en": "System Operation",              
        "value": "IOS 8"
    }, 
        {
        "en": "Memory Ram",              
        "value": "16 Gb"
    }, 
        {
        "en": "display", 
        "value": "5.5\""
    }]

and second problem is: how can I search product/s with the feature/s selected?
In mysql sure 🙂

Thank you!!

Best Answer

For the first problem, AFAICT, there is no way to use SQL to project certain properties of a JSON object. I think you will have to do that in the application layer.

For the second problem, how to search for products with a certain feature: I assume you have a table products with one row for each product. The table contains a column of type JSON which contains the features of the product as you have shown. If so, I think the following query should find the products where the value of the feature called "display" in English has the value of 5.5":

SELECT * FROM products 
WHERE JSON_EXTRACT(feature, 
    REPLACE(JSON_UNQUOTE(
        JSON_SEARCH(feature, 'one', 'display', NULL, '$.feature[*].en')),
        '.en', '.value')) = "5.5\"";

JSON_SEARCH will give you the path to the feature called 'display'. To find the value of the same feature, we replace '.en' in the path with '.value' and use JSON_EXTRACT to look up the value at this path. (JSON_UNQUOTE is needed to convert the JSON string from JSON_SEARCH to an SQL string to be passed to JSON_EXTRACT).

Related Question