0

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?

0

1 Answer 1

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.

2
  • For performance it should be possible to create a virtual indexed column from that JSON property - dev.mysql.com/doc/refman/5.7/en/….
    – jkavalik
    Commented Feb 28, 2016 at 10:49
  • Wow!!!! Thank you so much for help me!! :)
    – user112752
    Commented Feb 29, 2016 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.