How to search JSON data in MySQL?

106
votes

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":""
}}
9
mysqljsonmysql-json
i want to show me all record that key is "1" and "3" is one of values - reza
Explain "can't"! What output do you get? - Ajoy
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
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
why terrible idea.whats Disadvantages of this? - reza

9 Answers

180
votes

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

30
votes

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)
12
votes

  1. 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.

  2. Mysql 5.7 has builtin JSON functionality:
    http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

  3. Correct pattern is: 

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

    [^}] will match any character except }

11
votes

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.

4
votes

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)
3
votes

Please do it like.

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

If MySQL version < 5.7

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

// 70 = value

-3
votes

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[[:>:]]"';
-5
votes

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`;