Check If An Element Is Contained In The Values ​(array) Of A Json Column In Mysql

I have the following values ​​inside a cell of a json column in MySql: { 'produttori': [ '8', '9' ], 'articoli_alternativi': [ '3',

Solution 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.

Solution 2:

You can use

SELECT data
  FROM 
  (
    SELECT@i :=@i+1AS rn,
            JSON_UNQUOTE(JSON_EXTRACT(data,CONCAT('$.fornitori[',@i-1,']'))) AS elm,
           data               
      FROM information_schema.tables 
     CROSSJOIN articolo 
     CROSSJOIN (SELECT@i :=0) r
  ) q
 WHERE elm =8

in order to search for the spesific value within a spesific array("fornitori")

Demo