I have a table in MySQL that has a column that store JSON objects. How can I easily run queries that can have some of the JSON fields in the WHERE clause?
EX: With a table named articles
+----+---------+--------------------------------------------------------------------------------------------------+ | id | user_id | json_data | +----+---------+--------------------------------------------------------------------------------------------------+ | 1 | 1 | {"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"} | | 2 | 1 | {"url":"http://www.ebay.com/sch/CPUs-Processors-/164/i.html","title": "Computer and Processors"} | | 3 | 2 | {"url":"https://www.youtube.com/watch?v=tntOCGkgt98","title": "Funny Cats Compilation" | +----+---------+--------------------------------------------------------------------------------------------------+
I want to be able to write something like:
SELECT user_id, json_data FROM articles WHERE json_data.title LIKE "%CPU%"
That should return only the first row.
How to Retrieve data from JSON column in MySQL. MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are.
In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON.
MySQL NDB Cluster 7.5 (7.5. 2 and later) supports JSON columns and MySQL JSON functions, including creation of an index on a column generated from a JSON column as a workaround for being unable to index a JSON column. A maximum of 3 JSON columns per NDB table is supported.
You could use json_extract
(5.7 up). https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
SELECT user_id, json_data FROM articles WHERE json_extract(json_data, '$.title') LIKE '%CPU%';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With