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!

@wchiquito The one that is inside XAMPP. I don't know exactly, how can I retrieve this information? However the version of XAMPP is v3.2.4 — Memmo, Nov 04 '20 at 10:56
I edited the question above to make it more clear to readers that this is MariaDB, not MySQL. MariaDB has been slowly diverging from MySQL since 2010, so it should be considered a different software product with different features. — Bill Karwin, Nov 17 '20 at 02:57
@BillKarwin Thanks for your support! I thought XAMPP had MySql by default. Then investigating I found out that it had mariaDB. I have to get a culture on these technologies. — Memmo, Nov 17 '20 at 11:15

ΔO 'delta zero' · Accepted Answer · 2020-11-15T00:30:47.337

3

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

edited Nov 15 '20 at 00:30

answered Nov 10 '20 at 18:22

ΔO 'delta zero'

3,506
1
19
31

1

I have an error `com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '> '$.*[1]', '"Sacco da kg (Colli)"' )' at line 1`. This is my printed query: `SELECT COUNT(*) AS rowcount FROM ordine_al_fornitore WHERE JSON_CONTAINS(fca_ordinati -> '$.*[1]', '"Sacco da 5kg"' );` – Memmo Nov 12 '20 at 11:32
1

That strange, since it works on DB Fiddle. What version (`select @@version;`) are you running? – ΔO 'delta zero' Nov 12 '20 at 20:13
2

Also, you may try `WHERE JSON_CONTAINS(JSON_EXTRACT(fca_ordinati, '$.*[1]'), '"Sacco da 5kg"')`. – ΔO 'delta zero' Nov 12 '20 at 20:17
1

DB server: 10.4.11-MariaDB - mariadb.org – Memmo Nov 13 '20 at 11:38
However I point out that `JSON_CONTAINS` in other contexts has always worked for me, like [that](https://stackoverflow.com/questions/64535158/check-if-an-element-is-contained-in-the-values-array-of-a-json-column-in-mysq) – Memmo Nov 13 '20 at 11:43
2

Glad it works :-) My edited answer should now be correct even for MariaDB. The `->` operator is just MySQL's alias on `JSON_EXTRACT`, which is not supported by MariaDB. PS: Always start with platform and version, please :-) You've asked about MySQL, but you test answers against MariaDB. – ΔO 'delta zero' Nov 15 '20 at 00:34

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

1 Answers1