Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can write queries in MySQL that can parse JSON data in a column?

Tags:

json

mysql

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.

like image 713
Chris Cinelli Avatar asked Mar 19 '15 05:03

Chris Cinelli


People also ask

How do I query a JSON column in MySQL?

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.

Can MySQL parse JSON?

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.

Does MySQL support JSON columns?

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.


1 Answers

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%'; 
like image 65
northtree Avatar answered Sep 22 '22 13:09

northtree