Cosmos DB Group By

Question

Hi there,

We have a requirement, where each of our equipment are sending data and we have to get the first data point of each day and we have to compare it with some other data. So when we are calling the SDK for data I want group the all the equipment and only get the first data which I think will solve my problem, but I couldn't find any way to do it.

If anyone has some other solution please suggest me as well. For more info, Basically we are running Change DB feature of cosmos to calculate the consumption for each day by comparing the first data point of that day.

Thanks

I ran this query

SELECT
c.equipmentId,
c.engineInfo.currentEngineSeconds,
c.providerDatetime
FROM c
WHERE c.equipmentId IN ('H106180', 'H106128', 'G75-54')

to get this value

[
{
"equipmentId": "H106128",
"currentEngineSeconds": 44868960,
"providerDatetime": "2020-05-26T04:43:01"
},
{
"equipmentId": "H106180",
"currentEngineSeconds": 32282640,
"providerDatetime": "2020-05-26T04:43:01"
},
{
"equipmentId": "G75-54",
"currentEngineSeconds": 37096560,
"providerDatetime": "2020-05-26T04:45:18"
},
{
"equipmentId": "H106128",
"currentEngineSeconds": 44895240,
"providerDatetime": "2020-05-26T11:43:01"
},
{
"equipmentId": "G75-54",
"currentEngineSeconds": 33851160,
"providerDatetime": "2020-05-26T11:30:18"
},
{
"equipmentId": "H106180",
"currentEngineSeconds": 32307840,
"providerDatetime": "2020-05-26T11:43:01"
},
{
"equipmentId": "H106180",
"currentEngineSeconds": 32311440,
"providerDatetime": "2020-05-26T12:43:01"
},
{
"equipmentId": "H106128",
"currentEngineSeconds": 44898840,
"providerDatetime": "2020-05-26T12:43:01"
},
{
"equipmentId": "G75-54",
"currentEngineSeconds": 33855480,
"providerDatetime": "2020-05-26T12:45:18"
},
{
"equipmentId": "H106180",
"currentEngineSeconds": 32313960,
"providerDatetime": "2020-05-26T13:28:01"
},
{
"equipmentId": "H106128",
"currentEngineSeconds": 44900640,
"providerDatetime": "2020-05-26T13:28:00"
},
{
"equipmentId": "G75-54",
"currentEngineSeconds": 33858360,
"providerDatetime": "2020-05-26T13:30:18"
}
]

But I want to get, Basically the first value of the day. check providerDateTime

[
{
"equipmentId": "H106128",
"currentEngineSeconds": 44868960,
"providerDatetime": "2020-05-26T04:43:01"
},
{
"equipmentId": "H106180",
"currentEngineSeconds": 32282640,
"providerDatetime": "2020-05-26T04:43:01"
},
{
"equipmentId": "G75-54",
"currentEngineSeconds": 37096560,
"providerDatetime": "2020-05-26T04:45:18"
}
]
I could've take the TOP(3) value but i don't how many times each equipment will report. That's why i wanted to do groupBy and take the first value of each group.

This is the old thread,
https://social.msdn.microsoft.com/Forums/en-US/a140e11a-59e3-4a46-9a16-eb242a44b5ee/cosmos-db-group-by?forum=azurecosmosdb#2cfbea9a-6fdc-4c97-96f2-930c0dc0f3bc

Accepted Answer

Hi @HasinEshayat-7121,

I do remember this topic being discussed and thank you for following up. There were two solutions offered:

The first would be feasible only for small number of pairs (hundreds or few thousands):

SELECT  
    c.equipmentId,  
    c.currentEngineSeconds,  
    c.providerDatetime  
FROM c   
 WHERE (c.providerDateTime =  AND c.equipmentId = ) OR  
    (c.providerDateTime =  AND c.equipmentId = ) OR …

The second option is a two step query:

Step 1 - Generate equipmentId list.

SELECT  
    MIN(c.providerDatetime) AS minTime  
FROM c  
GROUP BY c.equipmentId

And the second step is to loop through the list from step 1.

Step 2 - Return currentEngineSeconds using equipmentId and minTime.

SELECT  
    c.equipmentId,  
    c.currentEngineSeconds,  
    c.providerDatetime  
FROM c   
 WHERE c.providerDateTime =  AND c.equipmentId =

These examples were provided by the product group and based upon the json examples you had, this was suggested.

Share via

Cosmos DB Group By

0 additional answers

Your answer