user112752 user112752 - 7 months ago 88
SQL Question

mysql 5.7 json_extract with key for all results in products table




I need filter products through the json field features:

id | name_product | features|



field features:

[{
"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\""
}]


Some products have some features in common, for filter I try this:

SELECT * FROM products WHERE json_extract(features,'$[*].value') = "IOS 8";



and:

SELECT * FROM products WHERE 'IOS 8' = json_extract(features,'$[*].value');


but the result is the same: NULL

If I put location in array instead of *:

SELECT * FROM products WHERE json_extract(feature,'$[1].value') = "IOS 8";


I have the result, the problem is: I dont know where is in exactly the position. In some products
$[1].value
in others
is $[2].value
in others
$[10].value


$[*].value
is the best but is null :(

How can fix this problem?

Thank you!!

Answer

Try:

mysql> DROP TABLE IF EXISTS `products`;
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE IF NOT EXISTS `products`(
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `name_product` VARCHAR(50),
    ->   `features` JSON,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> INSERT INTO `products`
    ->     (`name_product`, `features`)
    -> VALUES
    ->     ('product 1',
    ->      '[
    '>        {"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\'\'"}
    '>      ]'),
    ->      ('product 2',
    ->      '[
    '>        {"en": "System Operation", "it": "Sistema operativo", "value": "IOS 7"},
    '>        {"en": "Memory Ram", "it": "Memoria Ram", "value": "16 Gb"},
    '>        {"en": "display", "it": "schermo", "value": "5.5\'\'"}
    '>      ]'),
    ->      ('product 3',
    ->      '[
    '>        {"en": "Memory Ram", "it": "Memoria Ram", "value": "16 Gb"},
    '>        {"en": "display", "it": "schermo", "value": "5.5\'\'"},
    '>        {"en": "System Operation", "it": "Sistema operativo", "value": "IOS 8"}
    '>      ]');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT `id`, `name_product`, `features`
    -> FROM `products`
    -> WHERE JSON_SEARCH(`features`, 'all', 'IOS 8', NULL, '$[*].value') IS NOT NULL;
+----+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name_product | features                                                                                                                                                                                     |
+----+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | product 1    | [{"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''"}] |
|  3 | product 3    | [{"en": "Memory Ram", "it": "Memoria Ram", "value": "16 Gb"}, {"en": "display", "it": "schermo", "value": "5.5''"}, {"en": "System Operation", "it": "Sistema operativo", "value": "IOS 8"}] |
+----+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Comments