Details
Major Description
JSON_CONTAINS somehow modifies the JSON_ARRAYAGG result, transforming an array to a bidimensional array:
CREATE TABLE temp (v varchar(10), t int); |
|
|
INSERT INTO temp (v,t) VALUES ('A',1); |
INSERT INTO temp (v,t) VALUES ('B',1); |
INSERT INTO temp (v,t) VALUES ('B',2); |
INSERT INTO temp (v,t) VALUES ('C',2); |
INSERT INTO temp (v,t) VALUES ('C',3); |
|
|
SELECT JSON_ARRAYAGG(v) as array FROM temp GROUP BY t; |
+-----------+
|
| array |
|
+-----------+
|
| ["A","B"] |
|
| ["B","C"] |
|
| ["C"] |
|
+-----------+
|
3 rows in set (0.010 sec)
|
All perfect, until we try to make a JSON_CONTAINS:
SELECT JSON_ARRAYAGG(v) as array FROM temp GROUP BY t HAVING JSON_CONTAINS(array, JSON_QUOTE('B'),'$'); |
+-------------+
|
| array |
|
+-------------+
|
| [["A","B"]] |
|
| [["B","C"]] |
|
+-------------+
|
2 rows in set (0.006 sec)
|
The array column in the resulting rows is become a bidimensional array.
This doesn't happen on MySQL 8.