How to Query Cosmos DB graph by use of SQL CONTAINS

Question

I have a Cosmo DB graph where I would like to access the 'name' field in an expression using the string matching CONTAINS in Cosmos DB. CONTAINS works at 1 level as in matching CONATINS

1. SELECT s.label, s.name FROM s WHERE CONTAINS(LOWER(s.name._value), "cara") AND s.label = "site"

I also tried with a UDF function

2. SELECT s.label, s.name FROM s WHERE(s.label = 'site' AND udf.strContains(s.name._value, '/cara/i'))

I don't get any hits or syntax errors from Cosmos DB even that should be at least one record in this example. Does anyone have a hint? Thanks in advance

[
    {
        "label": "site",
        "name": [
            {
                "_value": "0315817 Caracol",
                "id": "2e2f000d-2e0a-435a-b472-75d257236558"
            }
        ]
    },
    {
        "label": "site",
        "name": [
            {
                "_value": "0315861 New Times",
                "id": "48497172-1734-43d0-9866-51faf9f603ed"
            }
        ]
    }
]

Answer 1

I noticed that the name property is an array not an object.So, you need to use join in sql.

SELECT s.label, s.name , name._value FROM s 
join name in s.name
where CONTAINS(LOWER(name._value), "cara") AND s.label = "site"

Output:

Hope it helps you.