CODEWITHSUNDEEP
mysqlsqljson
Júlio Pontes
Júlio Pontes

Reputation: 3

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

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       | 
+----+---------+

Upvotes: 0

Views: 216

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 2

Related Questions