get sum of values from mongodb database

Question

this is my document in the sensor collection

sensorId:1
sensorType:1
BuildingId:1
CalcItems:
    0: Object
        Ts:2019-08-30T18:30:00.000+00:00
        Value:1
    1:Object
        Ts:2019-08-31T19:00:00.000+00:00
        Value:2

i need to get sum of all value attributes with respect to same Ts date value

output like this

sensorType:1
BuildingId:1
CalcItems:
    0: Object
        Ts:2019-08-31T18:30:00.000+00:00
        Value:23
    1:Object
        Ts:2019-08-31T19:00:00.000+00:00
        Value:43

give me any suggestions

Is that needs to be grouped by sensorType, BuildingId & ts also? Seems like same sensor & building has different objects + value even on same day as they're separated by time !! — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Dec 2, 2019 at 17:31
@srinivasy, day should be change and i corrected it. there are different sensors in one building and there are many buildings. i need to get sum of the value of one sensor type in one building for each day. — Sanka Sanjeeva
– Sanka Sanjeeva, Commented Dec 3, 2019 at 3:22

hbamithkumara · Accepted Answer · 2019-12-03 04:10:16Z

1

Try this..

Sample live demo

[
  {
    "$unwind": "$CalcItems"
  },
  {
    $group: {
      _id: {
        "sensorType": "$sensorType",
        "BuildingId": "$BuildingId",
        "Ts": "$CalcItems.Ts"
      },
      "total": {
        "$sum": "$CalcItems.Value"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "sensorType": "$_id.sensorType",
      "BuildingIdVal": "$_id.BuildingId",
      "CalcItems.Ts": "$_id.Ts",
      "CalcItems.Value": "$total"
    }
  }
]

Reference

Mongodb $sum

Mongodb $group

Mongodb $project

edited Dec 3, 2019 at 4:10

answered Dec 3, 2019 at 3:59

hbamithkumara

2,5541 gold badge21 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Sanka Sanjeeva Over a year ago

Thank you very much for your reply, the calculation worked perfectly. Can you please tell me how to insert the sum of values for each date(Ts) inside the array CalcItems.Because when we project, two documents are made instead of inserting to the same array. Could you please help.

hbamithkumara Over a year ago

Can you post the result and sample data here

Sanka Sanjeeva Over a year ago

here sample data and result docs.google.com/document/d/…

hbamithkumara Over a year ago

Try this Updated demo

Sanka Sanjeeva Over a year ago

that answer worked brilliantly thank you so much it really helped me a lot Thank You

|

Collectives™ on Stack Overflow

get sum of values from mongodb database

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related