Writing complex JSON queries in MariaDB

I have a MariaDB database storing records containing JSON documents, and I need to write several queries against the contents of these documents.

Say our table is simply named table and it has two columns, a simple numeric id column and a data column holding the JSON document of each record.

The JSON documents have a structure like the following:

{
    "items": [
        {
            "foo": {
                "a": 10,
                "b": 10
            },
            "unrelated_property": "value"
        },
        {
            "foo": {
                "c": 5
            }
        },
        {
            "unrelated_property": "value"
        }
    ]
}

In essence, items is an array that may have zero or more elements. Of those elements, some may or may not have a foo key. If the foo key exists, it will be an object containing one or more arbitrary keys, each of which having a numeric value.

What I want is to be able to write a SELECT query that finds any JSON document record where:

• there is at least one item in items, with
• a foo key present, that contains
• at least one arbitrary key, where
• the value of that key is greater than zero

If this were a higher level language such as JavaScript, I'd be looking for something roughly equivalent to:

items.some(x => x.foo !== undefined && Array.from(Object.values(x.foo)).some(y => y > 0))

Is it possible to write this kind of query using only MariaDB's JSON functions? I can't seem to piece together any coherent arrangement of them that quite does what I want.

I realize this is extremely cursed. I'd be better off having this data in some kind of native relational format, or using a proper NoSQL solution that provides more flexible querying, or to just handling this outside of the database. I'm trying to work with the hand I've been dealt here. Changing which database I'm using or converting these JSON documents to a native relational structure are not options that are in the cards right now.

SELECT DISTINCT table.id
FROM table
CROSS JOIN JSON_TABLE(data,
                      '$.items[*].foo.*' COLUMNS (value INT PATH '$')) jsontable
WHERE jsontable.value > 0;

demo fiddle