data stored in map, need all values of all object in an array

Question

We have data like

[{
    "parameterId": "5f914ca2679bae721d38410b",
    "average": 574998.153846154,
    "count": 26.0,
    "date": "2020-09-08T18:30:00.000Z",
    "dataPerHour": {
        "0": {
            "min": 92570.0,
            "max": 995170.0,
            "avg": 578268.826086957,
            "count": 23,
            "date": "2020-09-04T19:07:41.000Z",
            "values": [{
                    "paramValue": "100414",
                    "time": "2020-09-04T19:07:41.000Z"
                },
                {
                    "paramValue": "705811",
                    "time": "2020-09-04T19:08:41.000Z"
                }
            ]
        },
        "1": {
            "min": 92570.0,
            "max": 995170.0,
            "avg": 678268.826086957,
            "count": 23,
            "date": "2020-09-03T19:07:41.000Z",
            "values": [{
                    "paramValue": "100414",
                    "time": "2020-09-03T19:07:41.000Z"
                },
                {
                    "paramValue": "705811",
                    "time": "2020-09-03T19:08:41.000Z"
                }
            ]
        }
    }
}, {
    "parameterId": "5f914ca2679bae721d38410b",
    "average": 574998.153846154,
    "count": 26.0,
    "date": "2020-09-08T18:30:00.000Z",
    "dataPerHour": {
        "0": {
            "min": 92570.0,
            "max": 995170.0,
            "avg": 778268.826086957,
            "count": 23,
            "date": "2020-09-04T19:07:41.000Z",
            "values": [{
                    "paramValue": "100414",
                    "time": "2020-09-04T19:07:41.000Z"
                },
                {
                    "paramValue": "705811",
                    "time": "2020-09-04T19:08:41.000Z"
                }
            ]
        }
    }
}]

We need output:

[
"2020-09-08T18:30:00" : "578268.826086957",

"2020-09-03T19:07:41" : "678268.826086957",

"2020-09-08T18:30:00" : "778268.826086957"
]

I need mongo query for this. I need data like key = date and value = avg of each data in dataPerHour.

score 1 · Accepted Answer · 2020-11-24 15:32:45Z

1

Does this help?

Playground

out

[
  {
    "data": [
      {
        "2020-09-03T19:07:41": 678268.826086957,
        "2020-09-04T19:07:41": 578268.826086957
      },
      {
        "2020-09-04T19:07:41": 778268.826086957
      }
    ]
  }
]

pipe

db.collection.aggregate([
  {
    $project: {
      data: {
        "$arrayToObject": {
          $map: {
            input: {
              "$objectToArray": "$dataPerHour"
            },
            as: "d",
            in: {
              $cond: [
                "$$d",
                [
                  {
                    "$dateToString": {
                      "date": {
                        $toDate: "$$d.v.date"
                      },
                      "format": "%Y-%m-%dT%H:%M:%S"
                    }
                  },
                  {
                    "$toDouble": "$$d.v.avg"
                  }
                ],
                "$$d"
              ]
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT.data"
      }
    }
  },
  {
    $unset: "_id"
  }
])

edited Nov 24, 2020 at 15:32

answered Nov 24, 2020 at 14:31

user12582392

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

2 Comments

PRAMOD KUMAR Over a year ago

the data can be 1000s and can be more than that ..............so i don't want to loop it again to make it in one object...

user12582392 Over a year ago

@PRAMODKUMAR believe me, there is no quicker way

Collectives™ on Stack Overflow

data stored in map, need all values of all object in an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related