CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Shanon Jackson
Shanon Jackson

Reputation: 6561

CosmoDB equivalent of this SQL

What's a good cosmodb sql equivalent to this? I have almost identical sql working on a dummy table in postgres, but can't seem to replicate it on cosmodb.

SELECT c.device_id FROM c
    WHERE (c.timestamp, c.device_id)
        IN (
            SELECT c.device_id, MAX(c.timestamp) FROM c WHERE c.device_id in ('00137A100000D2DB', '00137A100000D299') GROUP BY c.device_id
        )

results in the error:

 Gateway Failed to Retrieve Query Plan: Message: {"errors":[{"severity":"Error","location":{"start":49,"end":50},"code":"SC1001","message":"Syntax error, incorrect syntax near ','."}]}
ActivityId: ef246154-4a6b-4657-9cb3-8437a793053e, Microsoft.Azure.Documents.Common/2.14.0, Microsoft.Azure.Documents.Common/2.14.0

sample document

{
    "device_id": "00137A100000D299",
    "timestamp": 1602127299000,
    "battery": 3.6,
    "battery_unit": "V",
    "temperature": 0.76,
    "temperature_unit": "°C",
    "humidity": 36.28,
    "humidity_unit": "%",
    "id": "87340b02-2a5d-48db-9dff-97a14785cb7f"
}

Basically the idea is to get the latest timestamp for some known device ids, then use that to get the latest data at that timestamp.

Upvotes: 0

Views: 356

Answers (2)

Imre Pühvel
Imre Pühvel

Reputation: 5004

As mentioned by @martin-smith, you can't do cross doc magic with single query except simple aggregation.

To add a more practical note, if you happen to have just a single data point per timestamp, then your simplest option would be do just do a simple ordered query per device:

SELECT top 1 * FROM c
where c.device_id = @deviceId
order by timestamp desc

Quite often it cheaper and more maintainable to just send multiple simple and well-indexed queries than trying to optimize those 10ms requests to server by writing a complex SQL query.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453543

Cosmos DB SQL API does not allow any kind of lookups based on the result of a sub query or cross document joins (though lookups between documents are possible in the Mongo API and Graph API) - so you would need to do one query from the client and then use the values from that in a second query.

Or potentially you could adjust the SQL in my answer here to do it all in a single aggregate query.

Upvotes: 1

Related Questions