I'm new to mongodb.

My lookup give 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

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

CodePudding user response：

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