Group documents from multiple collections by date-CodePudding

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
]

CodePudding user response：

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