How to aggregate matching records in a day for a given condition with date and count values stored in an array

Question

i am trying to query a mongo collection to get results matching condition "status":new for each date and push the results to an array.

    I have tried the below query, but it is not grouping by date as expected. Please help.

Tried this:

  db.collection.aggregate([
      {
        $match: {
          status: "New"
        }
      },
      {
        $sort: {
          date: 1
        }
      },
      {
        $group: {
          _id: {
            status: "$status"
          },
          series: {
            $push: {
              name: "$Date",
              value: {
                $sum: 1
              }
            }
          }
        }
      },
      {
        $addFields: {
          name: "$_id.status"
        }
      }
    ])

Sample Collection and expected result:

    **Sample Collection:**

    [
      {
        "_id": "abcd123",
        "Date": "2018-06-08T09:42:36.000Z",
          "status": "New",
    },
    {
        "_id": "abcd124",
        "Date": "2018-06-09T09:42:36.000Z",
          "status": "New"
    },
    {
        "_id": "abcd125",
        "Date": "2018-06-09T09:31:44.000Z",
          "status": "New"
    },
    {
        "_id": "abcd126",
        "Date": "2018-06-10T09:42:43Z",
          "status": "New"
    },
    ]

    **Expected Result:**

[ { "_id": { "status": "New" }, "name": "New", "series": [ { "name": "2018-06-08T09:42:52Z", "value": 1 }, { "name": "2018-06-09T09:42:58Z", "value": 2 }, { "name": "2018-06-10T09:42:43Z", "value": 1 }, ] } ]

StackD StackD · Accepted Answer · 2018-11-20T07:32:46

@matthPen answered this earlier. Here: How to aggregate the sum for each distinct record on a given condition and get the result in an array in mongodb

I just had to add "addFields" to his answer. My mistake to not check his answer before asking it here.

db.collection.aggregate([
  {
    $match: {
      status: "New"
    }
  },
  {
    $group: {
      _id: "$Date",
      value: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: null,
      series: {
        $push: {
          name: "$_id",
          value: "$value"
        }
      }
    }
  },
  {
    $addFields: {
      name: "New"
    }
  },

])

Might help someone looking for similar query

How to aggregate matching records in a day for a given condition with date and count values stored in an array

1 Answers