Unexpected behavior of ARRAY_SLICE in Cosmos Db SQL API

I have Cosmos DB collection (called sample) containing the following documents:

[
    {
        "id": "id1",
        "messages": [
            {
                "messageId": "message1",
                "Text": "Value1"
            },
            {
            "messageId": "message2",
            "Text": "Value2"
            }
        ]
    },
        {
        "id": "id2",
        "messages": [
            {
                "messageId": "message3",
                "Text": "Value3"
            },
            {
                "messageId": "message4",
                "Text": "Value1"
            }
        ]
    },
        {
        "id": "id3",
        "messages": [
            {
                "messageId": "message5",
                "Text": "Value1"
            },
            {
                "messageId": "message6",
                "Text": "Value2"
            }
        ]
    },
        {
        "id": "id4",
        "messages": [
            {
                "messageId": "message7",
                "Text": "Value5"
             },
            {
                "messageId": "message8",
                "Text": "Value2"
            }
        ]
    },
]

I am trying to retrieve all the Documents, having messages and the first message has the field "Text"= 'Value1'. In this sample the documents with the ids '1' and '3' would be retrieved. Please notice that the document with id='id2' wouldn’t be retrieved, since the value of the text of the first message is 'Value3'.

The collection as mentioned is called sample and I am running the following Query:

"select sample.id, sample.messages, ARRAY_SLICE(sample.messages, 0, 1)[0].Text as valueOfText from sample"

As you can see in the first two images, I retrieve all Documents and every one of them have the field "valueOfText" set to value of the first message, as expected.

Now when I filter the collection (the third image), I retrieve no results at all. Is this an expected behavior?

Answer

Following your sql, got same results:

But why you have to use ARRAY_SLICE,it is used to return truncated array.Since your requirement is specific:

trying to retrieve all the Documents, having messages and the first message has the field “Text”= ‘Value1’

Just use sql:

SELECT c.id,c.messages,c.messages[0].Text as valueOfText FROM c
where  c.messages[0].Text = 'Value1'

Output:

Advertisement

Answer