CODEWITHSUNDEEP
mysqljson
user112752
user112752

Reputation: 131

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

Upvotes: 2

Views: 2321

Answers (1)

wchiquito
wchiquito

Reputation: 16549

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)

Upvotes: 3

Related Questions