0

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!!

  • 1
    If your data always has 3 values (en, it, value), then don't use JSON, use real columns. – Rick James Apr 16 '16 at 20:43
  • thank for anwser me...but if i have 100 milion products and each product have 10 features the risult is 1bilion for each language...and the data not is 3, dipend of language insert en,it,de,fr,es,.....and value...for this i think json...just for performance....if you have best solution tell me please :) – user112752 Apr 17 '16 at 9:56
1

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).

|improve this answer|||||

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.