How do I write a SQL query for a shown azure cosmos db documents?

Question

I have following documents in azure cosmos db collection.

// Document 1
{
    "c": {
        "firstName": "Robert"
    }
    "elements" : [
        {
            "a": "x2",
            "b": {
                "name": "yadda2",
                "id": 1
            }
        }
    ]
}

// Document 2
{
    "c": {
        "firstName": "Steve"
    }
    "elements" : [
        {
            "a": "x5",
            "b": {
                "name": "yadda2",
                "id": 4
            }
        },
        {
            "a": "x3",
            "b": {
                "name": "yadda8",
                "id": 5
            }
        },
    ]
}

// Document 3
{
    "c": {
        "firstName": "Johnson"
    }
    "elements" : [
        {
            "a": "x4",
            "b": {
                "name": "yadda28",
                "id": 25
            }
        },
        {
            "a": "x5",
            "b": {
                "name": "yadda30",
                "id": 37
            }
        },
    ]
}

I need to write a query that returns all documents which have "b" object whose name is "yadda2" (i.e. /elements/*/b/name=yadda2). In other words, this query should return document 1 and 2 but NOT 3.

I tried following but it did not work:

SELECT * FROM x where ARRAY_CONTAINS(x.elements, {b: { name: "yadda2"}})

What am I doing wrong?

Answer 1

Just modify your sql to :

SELECT * FROM x where ARRAY_CONTAINS(x.elements, {b: { name: "yadda2"}},true)

Result:

Based on the official doc , the boolean expression could specify if the match is full or partial.

Hope it helps you.