CODEWITHSUNDEEP
mysqlsqljson
user3003830
user3003830

Reputation: 21

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

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

Upvotes: 1

Views: 1233

Answers (2)

Cid
Cid

Reputation: 15257

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

Upvotes: 1

GMB
GMB

Reputation: 222652

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.

Upvotes: 2

Related Questions