Count how many rows have a nested value in the arrays of a json column in MariaDB 10.4

Question

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

{
    "0": [
        21,
        "Pallet da 1250kg (50 * Sacco da 25kg)",
        "1250",
        "kg"
    ],
    "1": [
        21,
        "Sfuso",
        "12",
        "kg"
    ],
    "2": [
        12,
        "Sacco da 5kg",
        "10",
        "kg"
    ],
    "3": [
        12,
        "Pallet da 2500kg (2 * Pallet da 1250kg (50 * Sacco da 25kg))",
        "5000",
        "kg"
    ]
}

The keys ("0", "1", "2", "3") are automatically generated.
I would like to count the number of rows that have the second element of each array identical to the one I pass in the condition.
For now I'm capable of doing such a thing:

query = '''SELECT COUNT(*) AS rowcount FROM ordine_al_fornitore WHERE JSON_CONTAINS(fca_ordinati, '"''' + myVar + '''"', '$.[*]')'''

Which print is:

SELECT COUNT(*) AS rowcount FROM ordine_al_fornitore WHERE JSON_CONTAINS(fca_ordinati, '"Sacco da 5kg"', '$.[*]')

I just know how to pass the key in a fixed way ($.[*]), while actually I would like to iter through the keys to check if that value exists in cell 1 of the array (and consequently count). I would like to know how I can improve my query.

Thanks in advance!

Answer 1

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