MongoDB - Lookup match with condition array of object with string

Question

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

Yong Shun · Accepted Answer · 2022-09-02 06:21:20Z

1

Concerns:

You should compare the date as Date instead of string, hence you are required to convert the date strings to Date before comparing.
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

answered Sep 2, 2022 at 6:21

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aravinth E Over a year ago

this is working fine. but without pipeline its possible?

Yong Shun Over a year ago

Probably yes, but more complex. Demo

Collectives™ on Stack Overflow

MongoDB - Lookup match with condition array of object with string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related