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
– reza, Commented May 23, 2015 at 9: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
– reza, Commented May 23, 2015 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
– diggersworld, Commented May 23, 2015 at 10:53

informatik01 · Accepted Answer · 2018-06-06 13:12:33Z

295

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 6, 2018 at 13:12

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Aug 26, 2016 at 10:17

Sachin Vairagi

5,3704 gold badges39 silver badges65 bronze badges

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

4 Comments

Arnis Juraga Over a year ago

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

Robin van Baalen Over a year ago

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

Oba Api Over a year ago

I guess not possible for MySQL version 4.8.5? So then I'll have to look into a PHP solution.

איש נחמד Over a year ago

Doesn't the double call to JSON_EXTRACT increase resource usage? Why not use HAVING instead of WHERE?

chrki · Accepted Answer · 2016-12-24 16:51:43Z

50

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, 2016 at 16:51

chrki

6,3436 gold badges39 silver badges60 bronze badges

answered Sep 1, 2016 at 12:10

Vishnu Prasanth Gangisetty

1,25113 silver badges12 bronze badges

5 Comments

Sérgio Over a year ago

what do you mean with latest version of mysql ? in what version it is supported ?

Sérgio Over a year ago

in what version it is IN operator supported ?

Vishnu Prasanth Gangisetty Over a year ago

MySQL 5.5 and above @Sérgio

Sérgio Over a year ago

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'")

Salman Arshad Over a year ago

This is just wrong.

Bang Andre · Accepted Answer · 2021-04-02 14:32:03Z

16

If MySQL version < 5.7

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

// 70 = value

answered Apr 2, 2021 at 14:32

Bang Andre

5811 gold badge8 silver badges13 bronze badges

Comments

Valentino · Accepted Answer · 2015-08-04 21:59:11Z

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 4, 2015 at 21:59

Valentino

1651 silver badge3 bronze badges

2 Comments

tmarois Over a year ago

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!

Volatil3 Over a year ago

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

shA.t · Accepted Answer · 2018-01-01 10:14:19Z

12

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 1, 2018 at 10:14

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

answered May 23, 2015 at 10:57

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

3 Comments

chrisl08 Over a year ago

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.

eweb Over a year ago

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 Over a year ago

May I suggest JSON_EXTRACT(attribs_json, '$.feature."1".value') will get at the value of the feature at key "1"

Chandan · Accepted Answer · 2020-12-28 10:28:28Z

12

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)

edited Dec 28, 2020 at 10:28

Chandan

11.8k1 gold badge10 silver badges25 bronze badges

answered Dec 28, 2020 at 3:00

user3548865

1412 silver badges5 bronze badges

Comments

Nigel Ren · Accepted Answer · 2021-05-08 12:18:07Z

11

Please do it like.

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

edited May 8, 2021 at 12:18

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

answered May 8, 2021 at 9:31

Bhagwat Singh

1491 silver badge2 bronze badges

1 Comment

Erlang Parasu Over a year ago

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

Ram Pukar · Accepted Answer · 2021-11-10 10:24:17Z

5

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;

answered Nov 10, 2021 at 10:24

Ram Pukar

1,63117 silver badges20 bronze badges

Comments

Zaraki Kenpachi · Accepted Answer · 2020-01-28 07:25:02Z

-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, 2020 at 7:25

Zaraki Kenpachi

5,7802 gold badges17 silver badges40 bronze badges

answered Jan 28, 2020 at 6:46

romantic zang

5

1 Comment

MBorg Over a year ago

This is exactly the same as one of the already existing answers. There's no need to repeat what Valentino said :)

0x00 · Accepted Answer · 2018-08-28 05:15:47Z

-6

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

edited Aug 28, 2018 at 5:15

answered Aug 27, 2018 at 16:15

0x00

191 bronze badge

Collectives™ on Stack Overflow

How to search JSON data in MySQL?

10 Answers 10

4 Comments

5 Comments

Comments

2 Comments

3 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

4 Comments

5 Comments

Comments

2 Comments

3 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related