How to check JSON Array value contains a list of numbers and return length

Question

I have a MySQL table with the following definition:

+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | PRI | NULL    |       |
| numbers| json    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

The table has some sample data as follows:

+----+------+----------------------------------+
| id | numbers                                 |
+----+------+----------------------------------+
|  1 | [1,2,3,4,5]                             |
|  2 | [2,3,7,8,9]                             |
|  3 | [5,7,10,15]                             |
+----+------+----------------------------------+

How can I calculate the total number of matched numbers (2,3,7,10) for each card? For example:

+----+---------+
| ID | Matches |
+----+---------+
| 1  | 2       |
| 2  | 3       |
| 3  | 2       | 
+----+---------+

If numbers is equal [2,2] then you need the result of 1 or 2? Do not tell "the array cannot have duplicated values" - or prove this by table's CREATE TABLE with according CHECK constraint. — Akina
– Akina, Commented Oct 6, 2020 at 5:31

sticky bit · Accepted Answer · 2020-10-06 11:05:57Z

2

In MySQL 8+ you can cross join json_table() to get a row for each number ID combination (for the numbers in the JSON array of the ID). Then you can aggregate grouping by the ID and get the sum of instances where the number (from the JSON array) is in the list of numbers you search for.

SELECT t.id,
       sum(jt.number IN (2, 3, 7, 10)) matches
       FROM elbat t
            CROSS JOIN json_table(numbers, '$[*]' COLUMNS (number integer PATH '$')) jt
       GROUP BY t.id;

db<>fiddle

edited Oct 6, 2020 at 11:05

answered Oct 6, 2020 at 0:52

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

Júlio Pontes Over a year ago

If need to check same logic in two other columns how to write that SQL? Lets say columns: line1, line2, line3.

sticky bit Over a year ago

@JúlioPontes: That's another, new question you might want to ask.

Collectives™ on Stack Overflow

How to check JSON Array value contains a list of numbers and return length

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related