How to use aggregation operation of mongoDB in JSON data

Question

Suppose this is a input JSON data and collection name is place.

  [
    {
        "time": ISODate("2016-11-27T011:43:01.000+05:30"), 'userId': 'abc'
    },
    {
        "time": ISODate("2016-11-28T01:43:01.000+05:30"),'userId': 'pqr'
    },
    {
        "time": ISODate("2016-11-27T08:43:01.000+05:30"), 'userId': 'abc'
    },
    {
        "time": ISODate("2016-11-27T02:43:01.000+05:30"), 'userId': 'abc'
    },
    {
        "time": ISODate("2016-11-27T011:43:01.000+05:30"), 'userId': 'pqr'
    },
    {
        "time": ISODate("2016-11-28T011:43:01.000+05:30"), 'userId': 'abc'
    }
]

In above given JSON data, we have maintained user log in a collection. we have to find out the data when user firstly has logged corresponding with date. We have to perform operation on this json data so that output should be come in this format.

Output

[{"date": "2016-11-27",
  'user': [{'userId':'abc','time': ISODate("2016-11-27T08:43:01.000+05:30")},
            'userId':'pqr','time': ISODate("2016-11-27T11:43:01.000+05:30")]
 },
 {"date": "2016-11-28",
  'user': [{'userId':'abc','time': ISODate("2016-11-28T11:43:01.000+05:30")},
            'userId':'pqr','time': ISODate("2016-11-28T1:43:01.000+05:30")]
 }]

@Guys! please don't focus on date format.you print in any format — Rahul Saini, Sep 21 '17 at 18:24

score 1 · Answer 1 · answered Sep 21 '17 at 19:55

You can try below aggregation query.

db.collection_name.aggregate([
  {
    "$sort": {
      "userId": 1,
      "time": -1
    }
  },
  {
    "$group": {
      "_id": {
        "date": {
          "$dateToString": {
            "format": "%Y-%m-%d",
            "date": "$time"
          }
        },
        "userId": "$userId"
      },
      "time": {
        "$first": "$time"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.date",
      "user": {
        "$push": {
          "userId": "$_id.userId",
          "time": "$time"
        }
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  }
])

How to use aggregation operation of mongoDB in JSON data

1 Answers1