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

Question

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.

This might help stackoverflow.com/questions/26670022/…

Michael Petch
– Michael Petch

2015-03-19 05:44:20 +00:00
Commented Mar 19, 2015 at 5:44 — Michael Petch
– Michael Petch, Commented Mar 19, 2015 at 5:44
Use MySQL common_schema

Tim Biegeleisen
– Tim Biegeleisen

2015-03-19 05:44:52 +00:00
Commented Mar 19, 2015 at 5:44 — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 19, 2015 at 5:44

Luis Felipe de Melo · Accepted Answer · 2019-06-10 22:46:16Z

82

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%';

edited Jun 10, 2019 at 22:46

Luis Felipe de Melo

3695 silver badges11 bronze badges

answered Jun 26, 2018 at 5:27

northtree

9,45715 gold badges68 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Juan Aguillón Over a year ago

Is it a good idea? Make queries with json?

Deepak Yadav Over a year ago

This answer makes more sense according to me : stackoverflow.com/a/69523830/6668734

Fandi Susanto Over a year ago

Can also wrap the result with JSON_UNQUOTE to remove double quote.

Harikrushna Patel · Accepted Answer · 2021-12-13 09:08:24Z

29

Simple query example to extract json data,

This Will Works in MySQL AND MariaDB

1, JSON Object

SELECT JSON_EXTRACT('{"a":1,"b":"stringdata"}','$.b');

output:

"stringdata"

2,JSON Objct Multi Level

SELECT JSON_EXTRACT('{"a":1,"b":"stringdata","c":{"d":33}}','$.c.d')

output :

3, JSON Array

SELECT JSON_EXTRACT('[1,2,3,4]','$[0]')

Output:

4, JSON Multilevel Array

SELECT JSON_EXTRACT('[1,2,3,[4,5]]','$[3][1]');

output:

edited Dec 13, 2021 at 9:08

answered Oct 11, 2021 at 9:20

Harikrushna Patel

5417 silver badges12 bronze badges

3 Comments

Johann Over a year ago

If I store a Dictionary in JSON, such as [{"Key 1":5},{"Key 2":8}] is there any way to extract the first key if I don't know its name?

Harikrushna Patel Over a year ago

Use ` JSON_KEYS()` MySQL function.

Fandi Susanto Over a year ago

For case number 1, can also wrap the result with JSON_UNQUOTE to remove double quote.

Chris Cinelli · Accepted Answer · 2015-03-26 10:43:04Z

6

I ended up solving in this way: https://github.com/ChrisCinelli/mysql_json with a UDF. I detailed how to compile it and install it in the README. It works for me on Ubuntu 12.04.5 on gcc version 4.6.3 with MySQL 5.5

You will be able to 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%';

answered Mar 26, 2015 at 10:43

Chris Cinelli

4,8394 gold badges30 silver badges40 bronze badges

3 Comments

Praesagus Over a year ago

Anyone know if Mariahdb supports this?

coding-dude.com Over a year ago

I have 5.5.14 and FUNCTION json_get does not exist

Emmanuel Over a year ago

@coding-dude.com The json_get function is defined by the UDF (user-defined function) in the linked github page. It is not native to MySQL 5.5.

Tim Biegeleisen · Accepted Answer · 2015-03-19 05:49:17Z

3

Try the following query and see if it fits 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 newer.

answered Mar 19, 2015 at 5:49

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

Chris Cinelli Over a year ago

We use >= 5.5. I get: FUNCTION common_schema.extract_json_value does not exist. I think to use that I need to install: code.google.com/p/common-schema - I actually started looking to another plugin.

Gobona FellaH · Accepted Answer · 2022-06-29 00:48:49Z

2

SELECT json_extract(description, '$.contacts') AS contacts, json_extract(description, '$.name') AS name FROM orders WHERE 1

answered Jun 29, 2022 at 0:48

Gobona FellaH

214 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related