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?
Advertisement
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: