MongoDB aggregation lookup objects in specific date range

user_448662 0
Viewed 377

I have some users and orders made by them:

db={
  orders: [
    {
      "_id": "wJNEiSYwBd5ozGtLX",
      "orderId": 52713,
      "retailerId": 1320,
      "createdAt": ISODate("2020-01-31T04:34:13.790Z"),
      "status": "closed"
    },
    {
      "_id": "wJNEiSYwBd5ozGtLX2",
      "orderId": 52714,
      "retailerId": 1320,
      "createdAt": ISODate("2021-03-31T04:34:13.790Z"),
      "status": "closed"
    }
  ],
  users: [
    {
      "_id": "2gSznevqwkGTxLRvL",
      "createdAt": ISODate("2018-04-10T08:33:13.455Z"),
      "username": "retailer@gmail.com",
      "info": {
        "retailerId": 1320,
        
      },
      "settings": {},
      "status": "active",
      
    }
  ]
}

If I try to aggregate orders into users:

 db.users.aggregate([
      {
        "$lookup": {
          "from": "orders",
          "localField": "info.retailerId",
          "foreignField": "retailerId",
          "as": "orders"
        }
      },
    
    ])

I can get all orders merged into users like this:

[
  {
    "_id": "2gSznevqwkGTxLRvL",
    "createdAt": ISODate("2018-04-10T08:33:13.455Z"),
    "info": {
      "retailerId": 1320
    },
    "orders": [
      {
        "_id": "wJNEiSYwBd5ozGtLX",
        "createdAt": ISODate("2020-01-31T04:34:13.79Z"),
        "orderId": 52713,
        "retailerId": 1320,
        "status": "closed"
      },
      {
        "_id": "wJNEiSYwBd5ozGtLX2",
        "createdAt": ISODate("2021-03-31T04:34:13.79Z"),
        "orderId": 52714,
        "retailerId": 1320,
        "status": "closed"
      }
    ],
    "settings": {},
    "status": "active",
    "username": "retailer@gmail.com"
  }
]

But I want to only merge orders from last month, not all orders into users. How can I specify the date range?

https://mongoplayground.net/p/mZlDHQf2thN

mongodb-atlas aggregation-framework mongodb-query mongodb
1 Answers

Demo - https://mongoplayground.net/p/-hjGipqQPJq

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries

To perform uncorrelated subqueries between two collections as well as allow other join conditions besides a single equality match, the $lookup stage has the following syntax:

Use $lookup in the pipeline style -

db.users.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "let": { "rId": "$info.retailerId" },
      "pipeline": [
        {
          $match: {
            $expr: { $eq: [ "$retailerId","$$rId" ] },
            createdAt: { $gte: ISODate("2021-03-01T00:00:00.000Z"), $lt: ISODate("2021-04-01T00:00:00.000Z") } // write date range query here
          }
        }
      ],
      "as": "orders"
    }
  }
])
user_22242650
Related