Cosmos Db DateTime query in nested collection

Question

I am attempting to do some basic DateTime operations on a nested collection in Cosmos DB.

Here is an example of the document structure:

{
    "id": "9a8b63d1-0b35-477a-9eee-f44bd41cbbcc",
    "Name": "Some Name",
    "StoredReports": [
        {
            "Name": "Test 1234",
            "CreatedOn": "2019-07-24T07:26:51.2395361Z"
        },
        {
            "Name": "Test 1234",
            "CreatedOn": "2019-07-29T07:26:51.2395361Z"
        }
    ]
}

What I am attempting to do is just do some basic operations on the StoredReport.CreatedOn field.

Here is an example:

SELECT * FROM c WHERE c.StoredReport.CreatedOn > '2019-07-25T07:26:51.2395361Z'

But that is returning no records, and I would expect it to return the document but with only 1 StoredReport, I am assuming I am not querying this correctly due to it being a nested collection.

Answer 1

Firstly,your document contains StoredReports property and your sql querys StoredReport column.They are dismatch.

Then,StoredReports is an array, so you need to use EXISTS to query the nested document.

SELECT * 
FROM c
WHERE EXISTS (
    SELECT VALUE s 
    FROM s IN c.StoredReports 
    WHERE s.CreatedOn > '2019-07-25T07:26:51.2395361Z'
 ) 

See https://learn.microsoft.com/azure/cosmos-db/sql-query-subquery for more unwinding options.