CosmosDB - ARRAY_CONTAINS in ObjectArray check for two properties-CodePudding

I have a table in Cosmos with the following structure:

{
    "id": "123",
    "name": "test",
    "lastname": "test",
    "email": "[email protected]",
    "dni": "12345678",
    "username": "test",
    "role": "Admin",
    "costCenter": null,
    "password": "064d141b329ddabb0b0c8488b959b5b725c9e7b94b7f7a48a54076068bf88106",
    "Permissions": {},
    "AlertsConfiguration": [
        {
            "Code": "1",
            "isEnabled": true,
            "Details": "test"
        },
        {
            "Code": "2",
            "isEnabled": false,
            "Details": "test"
        },
        {
            "Code": "3",
            "isEnabled": false,
            "Details": "test"
        },
        {
            "Code": "4",
            "isEnabled": false,
            "Details": "test"
        },
        {
            "Code": "5",
            "isEnabled": false,
            "Details": "test"
        }
    ],
}

and I'm trying to get all the records of the table that matches in the AlertsConfiguration array with the condition of Code 2 and isEnabled true

I've tried the following approach:

SELECT c.id FROM c WHERE 
    (ARRAY_CONTAINS(c.AlertsConfiguration,{"Code": "2"},true) and
     ARRAY_CONTAINS(c.AlertsConfiguration,{"isEnabled":true},true))

But this returns all the records no matter if isEnabled is true or false

Any ideas?

CodePudding user response：

The issue is that each of your ARRAY_CONTAINS() clauses is applied to the entire AlertsConfiguration array. So, as long as a document has one array element with Code = "2" and one array element with isEnabled = true you'll get that document.

For both criteria to be applied to the same document element, change your ARRAY_CONTAINS() a bit:

SELECT c.id
FROM c
where array_contains(c.AlertsConfiguration, {"Code":"2", "isEnabled":true}, true)

This query will now only select documents where an array element has both Code = "2" and isEnabled = true.