ARRAY_CONTAINS query with single string values in CosmosDB

Question

Just for example lets say i have a document in Cosmos document DB looking something like this:

  {
"somename" : "myname"
"data": {
    "meta": {
        "versionId": "1",
        "lastUpdated": "somedate",
        "myStringArray": [
            "OneString",
            "AnotherString"
        ]
    }

}

}

I want to write a Cosmos SQL query where i can search for partial matches in "myStringArray". I have been trying to use ARRAY_CONTAINS but cant get this to work since it seems that its only looking at given values in the array.

For example

ARRAY_CONTAINS(data.meta.myStringArray, 'OneString')

Works for full match.

The examples i see for searching for partial match is

ARRAY_CONTAINS(data.meta.myStringArray, {'TheValueIDontHave' : 'OneStrin'}, true)

This obviously wont work since i only have single strings in "myStringArray".

Im guessing that i might be able to combine STARTSWITH with ARRAY_CONTAINS, or maybe apply some JOINS but im stuck and cant sort it out.

Is there any way in CosmosDb where i can search for a partial match for any values in "myStringArray"?

Appreciate all help i can get. Thanks

Answer 1

Can you please try this query:

SELECT VALUE c FROM c JOIN s in c.data.meta.myStringArray WHERE CONTAINS(s, "OneStrin")

SQL language reference for Azure Cosmos DB - ARRAY_CONTAINS

The boolean expression in ARRAY_CONTAINS (<arr_expr>, <expr> [, bool_expr]) checks for objects and not for substrings:

If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array.

The following example how to check for a partial match of a JSON in an array using ARRAY_CONTAINS.

SELECT  
ARRAY_CONTAINS([{"name": "apples", "fresh": true}, {"name": "strawberries", "fresh": true}], {"name": "apples"}, true), 
ARRAY_CONTAINS([{"name": "apples", "fresh": true}, {"name": "strawberries", "fresh": true}], {"name": "apples"}),
ARRAY_CONTAINS([{"name": "apples", "fresh": true}, {"name": "strawberries", "fresh": true}], {"name": "mangoes"}, true)

Here is the result set.

[{ 
"$1": true,
"$2": false,
"$3": false
}]