Mongodb Aggregation get Data per user-CodePudding

Report table sample data

    {
    "_id" : ObjectId("614415f4a6566a001623b622"),
    "record" : [ 
        {
            "dateTime" : ISODate("2021-09-17T04:13:39.465Z"),
            "status" : "time-in",
            "month" : 9,
            "day" : 17,
            "year" : 2021,
            "time" : 1631852019465.0,
            "date" : ISODate("2021-09-17T00:00:00.000Z"),
        },
        {
            "dateTime" : ISODate("2021-09-17T04:14:01.182Z"),
            "status" : "time-out",
            "month" : 9,
            "day" : 17,
            "year" : 2021,
            "time" : 1631852041182.0,
            "date" : ISODate("2021-09-17T00:00:00.000Z"),
        }
    ],
    "uid" : ObjectId("614415b0a6566a001623b80b"),
    "date" : ISODate("2021-09-17T00:00:00.000Z"),
    "status" : "time-out",
    "createdAt" : ISODate("2021-09-17T04:13:40.102Z"),
    "updatedAt" : ISODate("2021-09-17T04:14:01.831Z"),
    "__v" : 0
}

Users table sample data

{
"_id" : ObjectId("615c0f6db30aff375cd05ac1"),
"displayName" : "test test",
"firstName" : "test",
"lastName" : "test",
"email" : "[email protected]",
"brand" : "Jollibee",
"phone" : " 632312312312312",
"role" : 1,
"isVerified" : true,
"isArchived" : false,
"createdAt" : ISODate("2021-10-05T08:40:13.208Z"),
"updatedAt" : ISODate("2021-10-05T08:40:13.208Z"),
"__v" : 0

}

I have a data like this

db.getCollection('users').aggregate([
    { 
        "$match": { brand: "Jollibee" } 
    },
    {
          $lookup: {
              from: "orders",
              let: { id: 'id' },
              pipeline: [
                {
                    $match: {
                        date: { $gte: ISODate("2020-11-01"), $lt: ISODate("2021-11-31") },
                    }
                }
              ],
              as: "orders",
          },

    },
    {
      $project: {
        "_id": 1,
        "name": 1,
        "orders": 1
      }

    }
  ])

when I'm using this aggregation I'm getting all the data inserted per user.

What I want to happen is that. I will only get the data that belong to the user and not all the data of all users.

Added the sample documents for each collection

CodePudding user response：

You are not comparing the userIds of both collections. You should add that on your $match. Playground

db.users.aggregate([
  {
    "$match": {
      brand: "Jollibee"
    }
  },
  {
    $lookup: {
      from: "orders",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            date: {
              $gte: ISODate("2020-11-01"),
              $lt: ISODate("2021-11-30")
            },
            $expr: {
              $eq: [
                "$uid",
                "$$id"
              ]
            }
          }
        }
      ],
      as: "orders",
      
    },
    
  },
  {
    $project: {
      "_id": 1,
      "name": 1,
      "orders": 1
    }
  }
])