CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Amos Evange
Amos Evange

Reputation: 5

MongoDB - How to filter results of an lookup

I'm new to MongoDB.

My lookup gives me the following result, how can I filter through the results below in order to get expected_delivery_time, using MongoDB aggregate:

[
   {
      "from":"Jeddah",
      "delivery_rule":[
         {
            "to":"Makkah",
            "expected_delivery_time":3
         },
         {
            "to":"Riyadh",
            "expected_delivery_time":2
         }
      ]
   },
   {
      "from":"Riyadh",
      "delivery_rule":[
         {
            "to":"Makkah",
            "expected_delivery_time":3
         },
         {
            "to":"Riyadh",
            "expected_delivery_time":1
         }
      ]
   }
]

Below is my code:

{
    $lookup:
    {
        from: "Setting",
        pipeline: [
        {
            $match: {
                $expr: {
                    { $eq: ["$name", "delivery_rules"] }
                }
            }
        ],
        as: "delivery_rules"
    }
},
{ "$match": { "$delivery_rules.value.from": "Jeddah" } },
{ "$match": { "$delivery_rules.value.to": "Riyadh" } },

I need help with below MySQL equivalent to:

SELECT 'expected_delivery_time' WHERE from='Jeddah' AND to='Makkah'

Upvotes: 0

Views: 400

Answers (1)

Yong Shun
Yong Shun

Reputation: 51270

Based on the $lookup stage, I expected the result documents should be:

[
  {
    "from": "Jeddah",
    "delivery_rules": [
      {
        "to": "Makkah",
        "expected_delivery_time": 3
      },
      {
        "to": "Riyadh",
        "expected_delivery_time": 2
      }
    ]
  },
  {
    "from": "Riyadh",
    "delivery_rules": [
      {
        "to": "Makkah",
        "expected_delivery_time": 3
      },
      {
        "to": "Riyadh",
        "expected_delivery_time": 1
      }
    ]
  }
]

delivery_rules (with "s", but the document you shared is with delivery_rule)

Both delivery_rules.value.from and delivery_rules.value.to don't exist. And you shouldn't use $ for the field in $match stage based on your query.

  1. $lookup

  2. $match - Filter from and delivery_rules.to. Combine 2 $match stages into 1.

  3. $project - Decorate output document. Add expected_delivery_time field:

    3.1. $getField - Get expected_delivery_time field from the result 3.1.1.

    3.1.1. $first - Get the first value from the result 3.1.1.1 array.

    3.1.1.1. $filter - Filter the document with to is "Makkah" in delivery_rules array.

db.collection.aggregate([
  /* Lookup stage */
  {
    "$match": {
      "from": "Jeddah",
      "delivery_rules.to": "Riyadh"
    }
  },
  {
    $project: {
      expected_delivery_time: {
        "$getField": {
          "field": "expected_delivery_time",
          "input": {
            $first: {
              $filter: {
                input: "$delivery_rules",
                cond: {
                  $eq: [
                    "$$this.to",
                    "Makkah"
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

Sample Mongo Playground

Upvotes: 1

Related Questions