In order to do this, you need to serve JSON_CONTAINS a flat array of strings to search in.

a) JSON_EXTRACT(fca_ordinati, '$.*') to get an array of object’s values

b) JSON_EXTRACT(fca_ordinati, '$.*[1]') to get an array of each entry’s 2nd value (index 1)

c) JSON_CONTAINS(..., '"Sacco da 5kg"') search for string appearance in that array

SELECT COUNT(*)
FROM ordine_al_fornitore
WHERE JSON_CONTAINS(
  JSON_EXTRACT(fca_ordinati, '$.*[1]'),
  '"Sacco da 5kg"' -- note the string needs to have quotes
);

This works on MySQL 5.7.22 or newer: https://www.db-fiddle.com/f/bNyV8wMbNhF1qTWBCBt7un/0

And MariaDB 10.3 or newer: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=d1c3d750ee2ef58a60d234a58f0fc5d2

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top