Check if an element is contained in the values ​(array) of a json column in MySql

Question

I have the following values ​​inside a cell of a json column in MySql:

{
    "produttori": [
        "8",
        "9"
    ],
    "articoli_alternativi": [
        "3",
        "9"
    ],
    "articoli_accessori": [
        "5",
        "6",
        "7",
        "8"
    ],
    "tecnologie": [],
    "fornitori": [
        "9",
        "8"
    ],
    "classificazioni": [
        "3",
        "4"
    ]
}

I would like to make a query that extracts data based on the existence of a value in the array at the fornitori key.
For now I've tried this:

query = 'SELECT nome, formulati_commerciali FROM articolo WHERE JSON_CONTAINS(JSON_EXTRACT(dati, "$.fornitori"), "' + \
        value+'", "$")'

Which print is:

SELECT name, data FROM articolo WHERE JSON_CONTAINS(JSON_EXTRACT(data, "$.fornitori"), "8", "$")

Basically the condition is that value ("8") must be inside the fornitori list, otherwise skips the element.
Unfortunately, the query did not produce any results.
I would like to know how you can formulate such a query in MySql. I will need them often!

Thanks in advance!

Answer 1

This should do it:

SELECT name, data 
FROM articolo 
WHERE JSON_CONTAINS(data, '"8"', '$.fornitori')

The double quotes around 8 are important, in order to properly match the JSON data. On the other hand, the query consistently uses single quotes for string literals.