Selecting the latest document for each "Group"

user_960744 0
Viewed 908

I am using Azure Cosmos DB SQL API to try to achieve the following;

We have device data stored within a collection and would love to retrieve the latest event data per device serial effectively without having to do N queries for each device separately.

SELECT * 
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') ORDER BY c.EventEnqueuedUtcTime DESC

Im assuming I would need to use Group By - https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-group-by

Any assistance would be greatly appreciated

Rough example of data :

[
    {
        "temperature": 25.22063251827873,
        "humidity": 71.54208429695204,
        "serial": "V55555555",
        "testid": 1,
        "location": {
            "type": "Point",
            "coordinates": [
                30.843687,
                -29.789895
            ]
        },
        "EventProcessedUtcTime": "2020-09-07T12:04:34.5861918Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2020-09-07T12:04:34.4700000Z",
        "IoTHub": {
            "MessageId": null,
            "CorrelationId": null,
            "ConnectionDeviceId": "V55555555",
            "ConnectionDeviceGenerationId": "637323979596346475",
            "EnqueuedTime": "2020-09-07T12:04:34.0000000"
        },
        "Name": "admin",
        "id": "6dac491e-1f28-450d-bf97-3a15a0efaad8",
        "_rid": "i2UhAI7ofAo3AQAAAAAAAA==",
        "_self": "dbs/i2UhAA==/colls/i2UhAI7ofAo=/docs/i2UhAI7ofAo3AQAAAAAAAA==/",
        "_etag": "\"430131c1-0000-0100-0000-5f5621d80000\"",
        "_attachments": "attachments/",
        "_ts": 1599480280
    }
]

UPDATE: So doing the following returns the correct data but sadly you can only return data thats inside your group by or an aggregate function (i.e. cant do select *)

SELECT c.serial, MAX(c.EventProcessedUtcTime)
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') 
GROUP BY c.serial

[
    {
        "serial": "synap-aim-g1",
        "$1": "2020-09-09T06:29:42.6812629Z"
    },
    {
        "serial": "V55555555",
        "$1": "2020-09-07T12:04:34.5861918Z"
    }
]
azure-cosmosdb-sqlapi azure-cosmosdb group-by sql
2 Answers

Thanks for @AnuragSharma-MSFT's help:

We're glad that you resolved it in this way, thanks for sharing the update:

So doing the following returns the correct data but sadly you can only return data thats inside your group by or an aggregate function (i.e. cant do select *)

SELECT c.serial, MAX(c.EventProcessedUtcTime)
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') 
GROUP BY c.serial

[
    {
        "serial": "synap-aim-g1",
        "$1": "2020-09-09T06:29:42.6812629Z"
    },
    {
        "serial": "V55555555",
        "$1": "2020-09-07T12:04:34.5861918Z"
    }
]
user_105492810

If the question is really about an efficient approach to this particular query scenario, we can consider denormalization in cases where the query language itself doesn't offer an efficient solution. This guide on partitioning and modeling has a relevant section on getting the latest items in a feed.

We just need to get the 100 most recent posts, without the need to paginate through the entire data set.

So to optimize this last request, we introduce a third container to our design, entirely dedicated to serving this request. We denormalize our posts to that new feed container.

Following this approach, you could create a "Feed" or "LatestEvent" container dedicated to the "latest" query which uses the device serial as id and having a single partition key in order to guarantee that there is only one (the most recent) event item per device, and that it can be fetched by the device serial or listed with least possible cost using a simple query:

SELECT *
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1')

The change feed could be used to upsert the latest event, such that the latest event is created/overwritten in the "LatestEvent" container as its source item is created in the main.

user_24117130
Related