Search code examples
mongodbmongooseaggregation

Group documents from multiple collections by date


I have 3 schema with a basic structure

meal: {
    user: 'objectID',  
    createdAt: 'date
}

activity: {
    user: 'objectID',
    createdAt: 'date'
}


role: {
    user: 'objectID',
    createdAt: 'date'
}  

I would like to get all documents from each schema belonging to a user and group them by dates. For example, a response of

history: [
    {
        date: 01-11-2021,
        meal: [
            ...array of meal documents on 01-11-2021
        ],
        activity: [
            ...array of meal documents on 01-11-2021
        ],
        role: [
            ...array of meal documents on 01-11-2021
        ],
    },
    ...next date
]


Solution

  • data

    db={
      "user": [
        {
          "_id": 1,
          "name": "Sam"
        }
      ],
      "meal": [
        {
          "user": 1,
          "content": "apple",
          "createdAt": ISODate("2021-09-01T11:23:25.184Z")
        },
        {
          "user": 1,
          "content": "orange",
          "createdAt": ISODate("2021-09-01T11:23:25.184Z")
        },
        {
          "user": 1,
          "content": "pie",
          "createdAt": ISODate("2021-09-02T11:23:25.184Z")
        }
      ],
      "activity": [
        {
          "user": 1,
          "content": "baseball",
          "createdAt": ISODate("2021-09-01T11:23:25.184Z")
        }
      ],
      "role": [
        {
          "user": 1,
          "content": "admin",
          "createdAt": ISODate("2021-09-01T11:23:25.184Z")
        }
      ]
    }
    

    aggreagte

    db.user.aggregate([
      {
        "$match": {
          _id: 1
        }
      },
      {
        "$lookup": {
          "from": "meal",
          "localField": "_id",
          "foreignField": "user",
          "pipeline": [
            {
              "$set": {
                "from": "meal"
              }
            }
          ],
          "as": "meal_docs"
        }
      },
      {
        "$lookup": {
          "from": "activity",
          "localField": "_id",
          "foreignField": "user",
          "pipeline": [
            {
              "$set": {
                "from": "activity"
              }
            }
          ],
          "as": "activity_docs"
        }
      },
      {
        "$lookup": {
          "from": "role",
          "localField": "_id",
          "foreignField": "user",
          "pipeline": [
            {
              "$set": {
                "from": "role"
              }
            }
          ],
          "as": "role_docs"
        }
      },
      {
        $project: {
          user: "$name",
          items: {
            $concatArrays: [
              "$activity_docs",
              "$meal_docs",
              "$role_docs"
            ]
          }
        }
      },
      {
        "$unwind": "$items"
      },
      {
        $project: {
          createdAt: {
            $dateTrunc: {
              "date": "$items.createdAt",
              "unit": "day"
            }
          },
          content: "$items.content",
          from: "$items.from"
        }
      },
      {
        "$group": {
          "_id": {
            "createdAt": "$createdAt",
            "from": "$from"
          },
          "list": {
            "$push": "$$ROOT.content"
          }
        }
      },
      {
        "$group": {
          "_id": "$_id.createdAt",
          "documents": {
            "$push": {
              k: "$$ROOT._id.from",
              v: "$$ROOT.list"
            }
          }
        }
      },
      {
        "$project": {
          documents: {
            $arrayToObject: "$documents"
          }
        }
      },
      {
        "$group": {
          "_id": 1,
          "history": {
            "$push": {
              date: "$$ROOT._id",
              activity: "$$ROOT.documents.activity",
              meal: "$$ROOT.documents.meal",
              role: "$$ROOT.documents.role"
            }
          }
        }
      }
    ])
    

    mongoplayground