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

Question

I have a table in MySQL that has a column that stores JSON objects. How can I easily run queries that may have some JSON fields in a 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 write something like:

  SELECT user_id, json_data FROM articles WHERE json_data.title LIKE "%CPU%" 

This should only return the first row.

Answer 1

You can use json_extract (5.7 and above). 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%'; 

Answer 2

In the end, I decided: https://github.com/ChrisCinelli/mysql_json with UDF. I described in detail how to compile and install it in README. This works for me on Ubuntu 12.04.5 on gcc version 4.6.3 with MySQL 5.5

You can run:

 SELECT json_get('{"a":1}', 'a') => 1 SELECT json_get('{"a":1}', 'b') => NULL SELECT json_get('[1,2,3]', 2) => 3 SELECT json_get('{"a":[2]}', 'a', 0) => 2 #Also: SELECT json_get('{"a":{"b":2}}', 'a') => object SELECT json_get('{"a":[1,2,3]}', 'a') => array # Verify if it is a valid JSON: SELECT ISNULL(json_get('{"a":1}')); => 0 # Valid SELECT ISNULL(json_get('{"a":1')); => 1 # Invalid # Create an example table: CREATE TABLE 'message' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'data' text, PRIMARY KEY ('id') ); INSERT INTO message (id,data) VALUES(1,'{"from":"chris","title":"Awesome Article","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}'); INSERT INTO message (id,data) VALUES(2,'{"from":"loren","title":"Another Article","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}'); INSERT INTO message (id,data) VALUES(3,'{"from":"jason","title":"How to run a query","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}'); # Run queries on JSON values: SELECT json_get(data,'title') FROM message WHERE id=2; SELECT id,data FROM message WHERE json_get(data,'from')='chris'; SELECT id,data FROM message WHERE json_get(data,'title') LIKE '%Article%'; 

Answer 3

Try the following query and make sure it meets your needs:

 SELECT user_id, json_data FROM articles WHERE common_schema.extract_json_value(json_data,'title') LIKE "%CPU%" 

This will only work on MySQL version 5.1 or later.