JSON MySQL 5.7 query

Question

I have innodb table with a column in JSON format

My table products:

id_products      int
name_products    varchar --> fx-6350<br>
feature_products json    --> {"cache":{"l1":"128k","l2":"6Mb","l3":"8Mb"},"frequency":"3.9GHz","socket":am3+"}

How can I make a query with result "frequency" and "3.9GHz" and query for everything?

Answer 1

If I've understood you correctly, you want something like this

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

I created a table "products" - note that the last record corresponds to the one we want.

SELECT * FROM products;

id_products name_products   feature_products
1   blah    {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.97GHz"}
1   blah2   {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "4.7GHz"}
1   result  {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz}

Running the query above

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

gives:

id_products name_products   feature_products
1       result      {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz"}"

Which, as we can see, is what was required - result "frequency" and "3.9GHz" and query for everything

I found this reference helpful. And, if all else fails, one can always consult the documentation here :-). Also, the MySQL server team blog was useful.