MYSQL json query where array is present in json field likes

user21679533
April 19, 2023
134 views
0 votes
2 Answers

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

Answers

- Elikill58
- April 19, 2023 at 11:41 am
- 0 votes
0
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.
Login or Signup to reply.

- SelVazi
- April 19, 2023 at 11:47 am
- 0 votes
0
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
Login or Signup to reply.