Get max of grouped documents in CosmosDb

Question

I want to query Azure CosmosDb documents with SQL API query. These Documents shall be filtered and grouped by specific values. From these groups only the document with a specified max value shall be returned.

Example

Azure CosmosDb Documents

{
  "id": "1",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907312"
}

{
  "id": "2",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907314"
}

{
  "id": "3",
  "someValue": "shall be included",
  "group": "bar",
  "timestamp": "1668907312"
}

{
  "id": "4",
  "someValue": "don't include",
  "group": "bar",
  "timestamp": "1668907312"
}

Query

I want do get all documents

• with "someValue": "shall be included"
• grouped by group
• from group only max of timestamp

Example response

{
  "id": "2",
  "someValue": "shall be included",
  "group": "foo",
  "timestamp": "1668907314"
},
{
  "id": "3",
  "someValue": "shall be included",
  "group": "bar",
  "timestamp": "1668907312"
}

Question

What is the best way to do this? It would be optimal if

• it is possible in one query
• and executable with Azure SDK with use of SqlParameter (to prevent injection)

What i've tried

My current approach consists of 2 queries and uses ARRAY_CONTAINS, which does not allow the use of SqlParameter for the document paths.

{
  "id": "2",
  "some-value": "shall be included",
  "group": "foo",
  "timestamp": "1668907314"
}

First Query

SELECT c.group AS group
    MAX(c.timestamp) AS maxValue
    FROM c
    WHERE c.someValue = 'shall be included'
    GROUP BY c.group

Second Query

SELECT * FROM c WHERE ARRAY_CONTAINS(
        <RESULT-FIRST-QUERY>,
        {
        "group": c.group,
        "maxValue": c.timestamp
        },
        false
    )

Answer 1

I would utilize the MAX() function in conjunction with GROUP BY i.e

SELECT *
FROM c
WHERE c.someValue = "shall be included"
GROUP BY c.group
HAVING MAX(c.timestamp)

Haven't run that yet/need to make a collection, but seems like it should do the trick...