MYSQL json query where array is present in json field likes

Question

I wonder if this query is possible using json query.

I need to select all records that have more than 3 values a like. for example, I have an array [1,2,3,4,5,6].

Database record has:

# ["1","2","3","9","4","2"]     selected <--- this contains 1,2,3
# ["4","5","6","10","11","12"]  selected <--- this contains 4,5,6
# ["1","2","3","4","20","21"]   selected <--- this contains 1,2,3,4
4. ["1","2","21","22","23","24"]   not selected <-- only contains 1,2`

I was trying this query

select * from `combinations` where json_contains(`combination`, '"0"') and json_contains(`combination`, '"1"') and json_contains(`combination`, '"3"')

You are looking if value are lower than 7? Also, what is the issue with your query? — Elikill58, Apr 19 '23 at 09:03
im looking for records that contain any of this array [1,2,3,4,5,6] but i need 3 or more must be present. for example, 5,3,1 should be in the field or 2,1,6. — user21679533, Apr 19 '23 at 09:07
I made [an answer](https://stackoverflow.com/a/76053011/10952503). Check it then don't forget to accept it with the check mark on the top left of it :) — Elikill58, Apr 19 '23 at 11:34

score 0 · Answer 1 · answered Apr 19 '23 at 09:41

As you did, you should use json method from mysql. You need to use JSON_TABLE (require mysql 8.0+).

By doing (here with last line of your example)

SELECT *
FROM
    JSON_TABLE('["1","2","21","22","23","24"]', "$[*]" COLUMNS(myId INT PATH "$")) a

You will have this table:

myId
1
2
21
22
23
24

After getting this, you should count for values with COUNT(*) and a WHERE clause that filter. I'm using the IN case as you have a small amount of possible value, but feel free to use < for example.

Final query with everything:

SELECT
    (SELECT count(*)
        FROM JSON_TABLE(t.json, "$[*]" COLUMNS(myId INT PATH "$")) a
        WHERE myId IN (1, 2, 3, 4, 5, 6)
    ) as amount, json
FROM
    test t

Result:

amount	json
5	["1","2","3","9","4","2"]
3	["4","5","6","10","11","12"]
4	["1","2","3","4","20","21"]
2	["1","2","21","22","23","24"]

You can find example at DB fiddle.

score 0 · Answer 2 · answered Apr 19 '23 at 09:47

This can be done by splitting the array into rows using json_table then check if arrays contains more than 2 elements from (1,2,3,4,5,6) :

I used distinct to count elements only ones, you can remove it if you want to count duplicates

with cte as (
  select distinct t.myjson, j.combination
  from combinations t
  join json_table(
    t.myjson,
    '$[*]' columns (combination varchar(50) path '$')
  ) j
)
select myjson, count(*)
from cte
where combination in (1,2,3,4,5,6)
group by myjson
having count(*) > 2

Demo here

MYSQL json query where array is present in json field likes

2 Answers2