I have two collections "datasets" and "users".

I tried to lookup datasets.assignedTo = users.id that's working fine. Also, I want to match the field of datasets.firstBillable >= users.prices.beginDate date field are matched to get the current index price value. And also check users.prices.endDate is less than or equal to users.prices.beginDate.

For example:

cgPrices: 45

https://mongoplayground.net/p/YQps9EozlAL

Collections:

db={
  users: [
    {
      id: 1,
      name: "Aravinth",
      prices: [
        {
          beginDate: "2022-08-24T07:29:01.639Z",
          endDate: "2022-08-31T07:29:01.639Z",
          price: 45
        }
      ]
    },
    {
      id: 2,
      name: "Raja",
      prices: [
        {
          beginDate: "2022-07-25T07:29:01.639Z",
          endDate: "2022-07-30T07:29:01.639Z",
          price: 55
        }
      ]
    }
  ],
  datasets: [
    {
      color: "braun, rose gold",
      firstBillable: "2022-08-24T07:29:01.639Z",
      assignedTo: 1
    },
    {
      color: "beige, silber",
      firstBillable: "2022-07-25T07:29:01.639Z",
      assignedTo: 2
    }
  ]
}

My current implementation:

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      let: {
        assigned_to: "$assignedTo",
        first_billable: "$firstBillable"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$eq": [
                    "$id",
                    "$$assigned_to"
                  ]
                },
                {
                  "$gte": [
                    "$first_billable",
                    "$details.prices.beginDate"
                  ]
                },
                {
                  "$lte": [
                    "$first_billable",
                    "$details.prices.endDate"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "details": 0,
      "cg": {
        $first: {
          "$first": "$details.prices.price"
        }
      }
    }
  }
])

Output i needed:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "assignedTo": 1,
    "cg": 45,
    "color": "braun, rose gold",
    "details": 0,
    "firstBillable": "2022-08-24T07:29:01.639Z"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "assignedTo": 2,
    "cg": 55,
    "color": "beige, silber",
    "details": 0,
    "firstBillable": "2022-07-25T07:29:01.639Z"
  }
] 

https://mongoplayground.net/p/YQps9EozlAL

CodePudding user response：

Concerns:

1. You should compare the date as Date instead of string, hence you are required to convert the date strings to Date before comparing.

2. In users collection, prices is an array. You need to deconstruct the array to multiple documents first before compare the date fields in price.

The query should be:

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      let: {
        assigned_to: "$assignedTo",
        first_billable: {
          $toDate: "$firstBillable"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$id",
                "$$assigned_to"
              ]
            }
          }
        },
        {
          $unwind: "$prices"
        },
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$gte": [
                    "$$first_billable",
                    {
                      $toDate: "$prices.beginDate"
                    }
                  ]
                },
                {
                  "$lte": [
                    "$$first_billable",
                    {
                      $toDate: "$prices.endDate"
                    }
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "details": 0,
      "cg": {
        $first: "$details.prices.price"
      }
    }
  }
])

Demo @ Mongo Playground