How to search JSON data in MySQL?

Question

I have inserted records in mysql DB, with json encoded data type, Now I have to make search within json encoded data, but i am not able to get proper data using following MySql query.

SELECT  `id` ,  `attribs_json` 
FROM  `products` 
WHERE  `attribs_json` REGEXP  '"1":{"value":[^"3"$]'

Query results are key equal to "1" and value is anything except "3"

My data is:

{"feature":{"1":{"value":"["2","3"]"},
            "2":{"value":["1"]},
            "5":{"value":""},
            "3":{"value":["1"]},
            "9":{"value":""},
            "4":{"value":"\u0633\u0627\u062a\u0646"},
            "6":{"value":""},
            "7":{"value":""},
            "8":{"value":""}
           },
"show_counter":"0",
"show_counter_discount":""
}}

i want to show me all record that key is "1" and "3" is one of values — reza, May 23 '15 at 09:46
i want to show all products that feature's id is 1 and one of feature's values is 3 feature is array like this : feature = array( 1=>array(1,2,3),2=>array(1,4,7) ) i'm using jsonencode to save it to database — reza, May 23 '15 at 10:52
This sounds like a terrible idea. You would benefit by splitting the data you want to filter into their own columns, and then just using the JSON stuff for additional info you won't filter by. — diggersworld, May 23 '15 at 10:53

score 256 · Answer 1 · edited Jun 06 '18 at 13:12

256

If you have MySQL version >= 5.7, then you can try this:

SELECT JSON_EXTRACT(name, "$.id") AS name
FROM table
WHERE JSON_EXTRACT(name, "$.id") > 3

Output:

+-------------------------------+
| name                          | 
+-------------------------------+
| {"id": "4", "name": "Betty"}  | 
+-------------------------------+

Please check MySQL reference manual for more details:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

edited Jun 06 '18 at 13:12

informatik01

16,038
10
74
104

answered Aug 26 '16 at 10:17

Sachin Vairagi

4,894
4
35
61

2

Important note: `json_encode` saves integerst as double-quoated values (thus - `string` type). If searching for equal, `JSON_EXTRACT(name, "$.id") = "4"` must be used instead of `JSON_EXTRACT(name, "$.id") = 4` – Arnis Juraga Aug 14 '19 at 07:54
25

> In MySQL 5.7.9 and later, the -> operator serves as an alias for the JSON_EXTRACT() function when used with two arguments Your example then becomes: `SELECT name->"$.id" as name FROM table WHERE name->"$.id" > 3` Personally, I find this easier to follow. – Robin van Baalen Aug 19 '19 at 15:10
I guess not possible for MySQL version 4.8.5? So then I'll have to look into a PHP solution. – Oba Api Sep 20 '21 at 09:24
Doesn't the double call to `JSON_EXTRACT` increase resource usage? Why not use `HAVING` instead of `WHERE`? – איש נחמד Apr 24 '23 at 21:52

score 43 · Answer 2 · edited Dec 24 '16 at 16:51

43

If your are using MySQL Latest version following may help to reach your requirement.

select * from products where attribs_json->"$.feature.value[*]" in (1,3)

edited Dec 24 '16 at 16:51

chrki

6,143
6
35
55

answered Sep 01 '16 at 12:10

Vishnu Prasanth G

1,133
12
12

12

what do you mean with latest version of mysql ? in what version it is supported ? – Sérgio Jul 11 '17 at 14:37
in what version it is IN operator supported ? – Sérgio Jul 11 '17 at 14:42
3

MySQL 5.5 and above @Sérgio – Vishnu Prasanth G Jul 13 '17 at 09:37
1

mysql --version mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) Warning: (1235, "This version of MySQL doesn't yet support 'comparison of JSON in the IN operator'") – Sérgio Jul 13 '17 at 11:11
This is just wrong. – Salman A May 26 '23 at 12:28

score 14 · Answer 3 · answered Apr 02 '21 at 14:32

14

If MySQL version < 5.7

SELECT fields
FROM table
WHERE field_json LIKE '%"key":"70"%';

// 70 = value

answered Apr 02 '21 at 14:32

Bang Andre

471
7
11

score 13 · Answer 4 · edited Jan 01 '18 at 10:14

13

Storing JSON in database violates the first normal form.

The best thing you can do is to normalize and store features in another table. Then you will be able to use a much better looking and performing query with joins. Your JSON even resembles the table.
Mysql 5.7 has builtin JSON functionality:
http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

Correct pattern is:

WHERE  `attribs_json` REGEXP '"1":{"value":[^}]*"3"[^}]*}'

[^}] will match any character except }

edited Jan 01 '18 at 10:14

shA.t

16,580
5
54
111

answered May 23 '15 at 10:57

Naktibalda

13,705
5
35
51

8

Agreed on #1 above, but sometimes you have a case where few field names on a table are unknown at design time. This is a case where you can mix relational and nosql data storage by having a JSON datatype on the table. – chrisl08 May 26 '19 at 07:28
To point 1, the question wasn't about schema design it was about extracting data store as json. Point 2 a link to the answer, Point 3 is fragile in that it could fail if the json changes. – eweb Jan 19 '22 at 07:35
1

May I suggest JSON_EXTRACT(attribs_json, '$.feature."1".value') will get at the value of the feature at key "1" – eweb Jan 19 '22 at 07:50

score 13 · Answer 5 · answered Aug 04 '15 at 21:59

13

I use this query

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])key_word([^"])"';
or
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';

The first query I use it to search partial value. The second query I use it to search exact word.

answered Aug 04 '15 at 21:59

Valentino

165
1
3

MySQL 5.7 finally has JSON support. However will take a while for that update goes mainstream, but as a quick reference this is an easy way to extract some info. Thanks! – tmarois Jun 25 '17 at 16:59
It is not working for me. I have structure like `{"images":"-"}` and `SELECT id FROM parsed_redfin WHERE `data` RLIKE '"images":"[[:<:]]-[[:>:]]"';` is not returning anything – Volatil3 Oct 20 '17 at 18:36

score 10 · Answer 6 · edited Dec 28 '20 at 10:28

For Mysql8->

Query:

SELECT properties, properties->"$.price" FROM book where isbn='978-9730228236' and  JSON_EXTRACT(properties, "$.price") > 400;

Data:

mysql> select * from book\G;
*************************** 1. row ***************************
id: 1
isbn: 978-9730228236
properties: {"price": 44.99, "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon"}
1 row in set (0.00 sec)

score 8 · Answer 7 · edited May 08 '21 at 12:18

8

Please do it like.

SELECT * FROM `products` 
    WHERE JSON_UNQUOTE(JSON_EXTRACT(`attribs_json`, '$.value')) LIKE '%X%'

edited May 08 '21 at 12:18

Nigel Ren

56,122
11
43
55

answered May 08 '21 at 09:31

Bhagwat Singh

119
1
1

it may return wrong records of using `'%X%'` unquote if want to find exact value. For example records have value: `2, 52, 100, 200, 300`. We want to find value `2`. By using like `%2%`, we will get result: `2, 52, 200` instead of `2` – Erlang Parasu Dec 23 '22 at 02:19

score 5 · Answer 8 · answered Nov 10 '21 at 10:24

SELECT
    country.NAME AS 'country_name',
    city.NAME AS 'city_name',
    city.district,
    city.info,
    JSON_EXTRACT( city.info, "$.Population" ) AS 'formated_population' 
FROM
    city
    INNER JOIN country ON city.CountryCode = country.
    CODE INNER JOIN countrylanguage ON country.CODE = countrylanguage.CountryCode 
GROUP BY
    city.NAME,
    city.district,
    country.NAME;

ORDER BY
    country.NAME ASC;

score -4 · Answer 9 · edited Jan 28 '20 at 07:25

-4

I think...

Search partial value:

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])*key_word([^"])*"';

Search exact word:

SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';

edited Jan 28 '20 at 07:25

Zaraki Kenpachi

5,510
2
15
38

answered Jan 28 '20 at 06:46

romantic zang

5

1

This is exactly the same as one of the already existing answers. There's no need to repeat what Valentino said :) – MBorg Jan 28 '20 at 07:22

0x00 · Answer 10 · 2018-08-28T05:15:47.250

for MySQL all (and 5.7)

SELECT LOWER(TRIM(BOTH 0x22 FROM TRIM(BOTH 0x20 FROM SUBSTRING(SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed)))),LOCATE(0x22,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed))))),LENGTH(json_filed))))) AS result FROM `table`;

How to search JSON data in MySQL?

10 Answers10

Linked

Related