After some aggregation stages, I want to filter the array within the document that matches with the conditions

```I have 2 collections

1. external_S_P_FLAT_main_api 
2. external_S_C_FLAT_main_api

The collection has data as below.

"external_S_P_FLAT_main_api": [
  {
    "_id": {
      "$oid": "654c6a594d0867aef588674d"
    },
    "data.pricing.material": "TG11",
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000001",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef588674e"
    },
    "data.pricing.material": "TG12",
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000002",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef588674f"
    },
    "data.pricing.material": "TG14",
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000003",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef5886750"
    },
    "data.pricing.material": "TG2341",
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000004",
  }
]
"external_S_C_FLAT_main_api": [
  {
    "_id": {
      "$oid": "654c6a594d0867aef5886751"
    },
    "data.costcenter.controlling_area": "AJ00",
    "data.costcenter.cost_center": "DE000001",
    "data.costcenter.valid_from_date": "2023-09-12",
    "data.costcenter.long_description": "CC DE000001 - 3rd",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef5886752"
    },
    "data.costcenter.controlling_area": "AJ00",
    "data.costcenter.cost_center": "DE000002",
    "data.costcenter.valid_from_date": "2022-03-02",
    "data.costcenter.long_description": "CC DE000002 - 1st",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef5886753"
    },
    "data.costcenter.controlling_area": "AJ00",
    "data.costcenter.cost_center": "DE000003",
    "data.costcenter.valid_from_date": "2023-10-25",
    "data.costcenter.long_description": "CC DE000003 - 1st",
  },
  {
    "_id": {
      "$oid": "654c6a594d0867aef5886754"
    },
    "data.costcenter.controlling_area": "AJ00",
    "data.costcenter.cost_center": "DE000004",
    "data.costcenter.valid_from_date": "2023-10-25",
    "data.costcenter.long_description": "CC DE000004 - 2nd",
  }
]

Below is the query I am executing: 

db.external_S_P_FLAT_main_api.aggregate([
  {
    "$addFields": {
      "external_S_P_FLAT_main_api_data.pricing.controlling_area": "$data.pricing.controlling_area"
    }
  },
  {
    "$addFields": {
      "external_S_P_FLAT_main_api_data.pricing.cost_center": "$data.pricing.cost_center"
    }
  },
  {
    "$lookup": {
      from: "external_S_C_FLAT_main_api",
      let: {
        let_data__pricing__controlling_area: "$external_S_P_FLAT_main_api_data.pricing.controlling_area",
        let_data__pricing__cost_center: "$external_S_P_FLAT_main_api_data.pricing.cost_center"
      },
      pipeline: [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": [
                    "$data.costcenter.controlling_area",
                    "$$let_data__pricing__controlling_area"
                  ]
                },
                {
                  "$eq": [
                    "$data.costcenter.cost_center",
                    "$$let_data__pricing__cost_center"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "from_external_S_C_FLAT_main_api"
    }
  },
  {
    "$project": {
      _id: 0,
      "external_S_P_FLAT_main_api_data.pricing.controlling_area": 0,
      "external_S_P_FLAT_main_api_data.pricing.cost_center": 0,
      // from_external_S_C_FLAT_main_api: 0,
    }
  }
])

Below is the output comes: 

[
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000001",
    "data.pricing.material": "TG11",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886751"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.long_description": "CC DE000001 - 3rd",
        "data.costcenter.valid_from_date": "2023-09-12"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000002",
    "data.pricing.material": "TG12",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886751"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.long_description": "CC DE000001 - 3rd",
        "data.costcenter.valid_from_date": "2023-09-12"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000003",
    "data.pricing.material": "TG14",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886751"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.long_description": "CC DE000001 - 3rd",
        "data.costcenter.valid_from_date": "2023-09-12"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000004",
    "data.pricing.material": "TG2341",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886751"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.long_description": "CC DE000001 - 3rd",
        "data.costcenter.valid_from_date": "2023-09-12"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      },
      {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    ]
  }
]

I want the output in such a way that it has single element in the array from_external_S_C_FLAT_main_api which is matching with the condition as
value of data.pricing.cost_center matches with from_external_S_C_FLAT_main_api.data.costcenter.cost_center and 
value of data.pricing.controlling_area matches with from_external_S_C_FLAT_main_api.data.costcenter.controlling_area

So the expected output should be as below:
[
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000001",
    "data.pricing.material": "TG11",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886751"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.long_description": "CC DE000001 - 3rd",
        "data.costcenter.valid_from_date": "2023-09-12"
      }
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000002",
    "data.pricing.material": "TG12",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      },
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000003",
    "data.pricing.material": "TG14",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      },
    ]
  },
  {
    "data.pricing.controlling_area": "AJ00",
    "data.pricing.cost_center": "DE000004",
    "data.pricing.material": "TG2341",
    "external_S_P_FLAT_main_api_data": {
      "pricing": {}
    },
    "from_external_S_C_FLAT_main_api": [
      {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    ]
  }
]

What am I missing? How shall I get the expected result?```

To filter the array within the document you $project, $addFields or $set the array with $filter.

Is there any reasons why your documents look like:

rather than

    "data" : {
        "costcenter" : {
            "controlling_area": "AJ00",
            "cost_center": "DE000002",
            "valid_from_date": "2022-03-02",
            "long_description": "CC DE000002 - 1st",
        }
    }

I would be worry that the former structure takes more space. I would also be worry that you would need to project each field of data.costcenter individually.

1 Like

@steevej,

My documents look like this because I am storing the collection fields as dot notation.
I have a Mongo version of v4.4.3

Can you help me with the built query with the suggestions as below?
filter the array within the document you $project, $addFields or $set the array with $filter.

It looks like you have not clicked on the $filter link I provided. Usually, there is a counter that indicates the number of time the link is clicked and as this morning there is no counter, so no one has clicked on the link. So it means you have not looked at the examples shown in the documentation.

Example of $set with $filter

{ "$set" : {
    "from_external_S_C_FLAT_main_api" : { "$filter" : {
        "input" : "$from_external_S_C_FLAT_main_api" ,
        "cond" : { /* An expression that resolves to a boolean value used to determine if an element should be included in the output array.  */ }
    } }
} }

Why are you

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.