"Contains()" not generating correct SQL

[gthvidsten]

When you use .Contains() in a .Where() then a key/value list is generated using json_table() and a WHERE X IN is used to search in it.

Take this table:

ID (int) | ArrayValues (JSON)
-----------------------------
1        | [7,13,15]
2        | [4,8,12]
3        | [15,16,23]

With this entity:

public class MyTable
{
    public int Id { get; set; }
    public IList<int> ArrayValues { get; set;}
}

And the following code to get data:

DbContext.MyTable
    .Where(t => t.ArrayValues.Contains(15))
    .ToList();

Will generate the following SQL:

SELECT `m`.`Id`, `m`.`ArrayValues`
FROM `MyTable` AS `m`
WHERE 15 IN (
    SELECT `a`.`value`
    FROM JSON_TABLE(`m`.`ArrayValues`, '$[*]' COLUMNS (
        `key` FOR ORDINALITY,
        `value` int PATH '$[0]'
    )) AS `a`
)

This return zero rows when performed on the table above. I don't know exactly why it doesn't work. I've experimented a lot with the SQL but I've never gotten it to return anything.

What does work, however, is json_contains():

SELECT *
FROM MyTable
WHERE JSON_CONTAINS(ArrayValues, '15')

This returns the correct rows.

---

MySQL version: 8.0.31
Operating system: Windows Server 2016
Pomelo.EntityFrameworkCore.MySql version: 8.0.2
Microsoft.AspNetCore.App version: .net8.0