Return records where array is empty

SQL++
,
1

Hi,
How do I query records where one of the fields that is an array is empty. The structure of my document looks like:

[
        {
            "_CAS": 1556224896985268224,
            "_ID": "24be9252-13ca-4293-b9bf-6914ad0aec08",
            "_class": "me.mmonti.plex.media.photo.Photo",
            "absolutePath": "/Users/mauro.monti/photos/source1/photo5 copy.png",
            "created": 1556213808755,
            "filename": "photo5 copy.png",
            "metadata": {},
            "source": "b23a8c6b-8dc3-446c-bbe3-67aaaf0a60fe",
            "tags": []
    }
]

I tried something like:

SELECT ARRAY_LENGTH(plex.tags) AS tagsCount, META(`plex`).id AS _ID, META(`plex`).cas AS _CAS, `plex`.* 
FROM `plex`
WHERE `_class` = "me.mmonti.plex.media.photo.Photo" 
AND tagsCount > 0;

But got no results.
Any suggestion?

Thanks,
M.-

2

The order of evaluation is FROM—> WHERE —> Projection ----> ORDER BY----> OFFSET---->LIMIT.
Any alias names can be used only in the next operation. So projection alias can’t be used in WHERE clause.

SELECT ARRAY_LENGTH(plex.tags) AS tagsCount, META(`plex`).id AS _ID, META(`plex`).cas AS _CAS, `plex`.* 
FROM `plex`
WHERE `_class` = "me.mmonti.plex.media.photo.Photo" 
AND    ARRAY_LENGTH(plex.tags) > 0;
1 Like