Cosmos DB query with array

Question

We have looked at the docs and the only main thing we have found is the ARRAY_CONTAINS which does not address our problem as our issue is the inverse of this i.e. whether a string field contains elements of an array.

We are trying to create a query such that say a field called

Address has a value : "24 The Parkway, London W11AA".

We woud like to send in an array of strings as a query parameter such as:

["AA", "way", "Park", "24"]

We would like to make a query that if any/all of the elements of the array is in the Address field, return true else false.

Answer 1

Cosmos DB query with array

To work with an array in Cosmos DB. Try with the below query, it uses RegexMatch in WHERE condition as shown in the below query. It checks that all substrings are present in the Address field.

Sample data stored in Azure Cosmos DB.

[
    {
        "id": "1",
        "Address": "24 The Parkway, London W11AA",
        "AddressKeywords": [
            "24",
            "The",
            "Parkway",
            "London",
            "W11AA"
        ]
     
    },
    {
        "id": "2",
        "Address": "15 Elm Street, New York NY10001",
        "AddressKeywords": [
            "15",
            "Elm",
            "Street",
            "New",
            "York",
            "NY10001"
        ]
    },
    {
        "id": "3",
        "Address": "101 Main Road, Los Angeles CA90012",
        "AddressKeywords": [
            "101",
            "Main",
            "Road",
            "Los",
            "Angeles",
            "CA90012"
        ]
    }
]

Query I tried with:

SELECT * FROM c 
WHERE RegexMatch(c.Address, "(?=.*AA)(?=.*way)(?=.*Park)(?=.*24)")

Output:

[
    {
        "id": "1",
        "Address": "24 The Parkway, London W11AA",
        "AddressKeywords": [
            "24",
            "The",
            "Parkway",
            "London",
            "W11AA"
        ]
    }
]