How to search JSON data in MySQL by key and value?

Question

I am inserting my data in a database with json_encoded. Now I want to search in "feature", but the result is not exactly true.

MySQL query:

select * 
from `modul_69`  
where `extmod` like '%"68":%' 
  and `extmod` like '%"4"%' 
  and `extmod` not like '%"4":%'

Results:

row1 data:

{"68":["1","4","7"],"67":["14"],"75":["28"]} - true

row2 data:

{"68":["59"],"67":["1","11","13"],"75":["3","4","5","27"]} - false

I want select only row1 by key:68 and value:4

Please help

If you have a MySQL server version that allows you to use MySQL’s own JSON functionality - then use that. If you don’t - then don’t store data this way to begin with, but properly normalize. — C3roe
– C3roe, Commented Mar 4, 2020 at 9:21

GMB · Accepted Answer · 2020-03-04 09:26:35Z

2

Here is one way to do it using MySQL JSON functions, available since version 5.7:

select *
from t
where json_search(js -> '$."68"', 'one', '4') is not null

What this does is get the array that correspond to outer key '68' (using ->, which is a syntactic sugar for json_extract()), and then search its content with json_search(); if a non-null value is returned, we have a match.

answered Mar 4, 2020 at 9:26

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

Cid Over a year ago

That json_search() function is nice

user3003830 Over a year ago

Thanks for the answer, but MSQL version is 5.5

GMB Over a year ago

@user3003830: 5.5 has no JSON support. Just upgrade. Otherwise, I would recommend against storing JSON in that database, since you have no easy way to manipulate it with SQL.

Cid · Accepted Answer · 2020-03-04 09:39:58Z

To find if the value '"4"' is contained in the member '"68"', you can first extract the array using JSON_EXTRACT() :

SELECT JSON_EXTRACT(m.extmod, '$."68"')
FROM modul_69 m;

This outputs

["1", "4", "7"]
["59"]

To search in a JSON array if it contains a specific value, you can use JSON_CONTAINS() :

SELECT JSON_CONTAINS('["1", "4", "7"]', '"4"', '$'); -- output is 1
SELECT JSON_CONTAINS('["59"]', '"4"', '$'); -- output is 0

Now you can combine both functions to get the rows that contains the expected value :

Schema (MySQL v5.7)

CREATE TABLE modul_69
(
  extmod JSON
);

INSERT INTO modul_69 VALUES ('{"68":["1","4","7"],"67":["14"],"75":["28"]}'), ('{"68":["59"],"67":["1","11","13"],"75":["3","4","5","27"]}');

Query #1

SELECT *
FROM modul_69 m
WHERE JSON_CONTAINS(JSON_EXTRACT(m.extmod, '$."68"'),
                    '"4"',
                    '$') = 1;

Output

| extmod                                              |
| --------------------------------------------------- |
| {"67": ["14"], "68": ["1", "4", "7"], "75": ["28"]} |

View on DB Fiddle

Collectives™ on Stack Overflow

How to search JSON data in MySQL by key and value?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related