mongodb aggergate with nested arrays of objects inside an array of objects

Question

I got following data structure:

data = {
    "_id": "abc",
    "field": "value",
    "misc": {
        "height": 1.1,
    },
    "sales": [
        {
            "label": 1,
            "codes": [
                {
                    "code": 123456,
                    "active": True
                },
                {
                    "code": 234567,
                    "active": False
                },
                {
                    "code": 345678,
                    "active": False
                },
            ],
            "prices": [
                {
                    "price": 2.99,
                    "valid_from": datetime(year=2023, month=3, day=1)
                },
                {
                    "price": 3.99,
                    "valid_from": datetime(year=2023, month=4, day=1)
                },
                {
                    "price": 4.99,
                    "valid_from": datetime(year=2023, month=5, day=1)
                },
                {
                    "price": 5.99,
                    "valid_from": datetime(year=2023, month=6, day=1)
                },
            ]
        },
        {
            "label": 2,
            "codes": [
                {
                    "code": 987654,
                    "active": True
                },
                {
                    "code": 876543,
                    "active": False
                },
                {
                    "code": 765432,
                    "active": False
                },
            ],
            "prices": [
                {
                    "price": 2.99,
                    "valid_from": datetime(year=2023, month=3, day=1)
                },
                {
                    "price": 3.99,
                    "valid_from": datetime(year=2023, month=4, day=1)
                },
                {
                    "price": 4.99,
                    "valid_from": datetime(year=2023, month=5, day=1)
                },
                {
                    "price": 6.99,
                    "valid_from": datetime(year=2023, month=6, day=1)
                },
            ]
        },

    ]
}

My Goal is to output the label 1 object. This Filter Aggregate works for me:

db.MasterData.aggregate([
    
    { "$match": { "_id": "abc" } },

    {
        "$project": {
            "field": 1,
            "sales": {
                "$filter": {
                    "input": "$sales",
                    "as": "item",
                    "cond": {
                        "$eq": ["$$item.label", 1]
                    }
                }
            },
        }
    }
    
])

But I want also to filter the nested arrays codes and prices, for example show only the code where active is True and only the prices which are greater than today. But I couldn’t figure out how to access the nested arrays. This query results in an empty outcome:

db.MasterData.aggregate([
    
    { "$match": { "_id": "abc" } },

    {
        "$project": {
            "field": 1,
            "sales.codes": {
                "$filter": {
                    "input": "$sales.codes",
                    "as": "item",
                    "cond": {
                        "$eq": ["$$item.active", True]
                    }
                }
            },
        }
    }
    
])

Can someone help me with this please?

Also, I am wondering if this is the best way to deal with this Data structure. Would it be better to insert the sales Part in another collection with reference to the Main Data? As far as I know at this point of my MongoDB journey, using reference documents should be avoid when it could be.

Answer 1

Maybe something like this:

db.collection.aggregate([
{
  "$match": {
    "_id": "abc"
 }
 },
 {
"$addFields": {
  "sales": {
    "$filter": {
      "input": "$sales",
      "as": "s",
      "cond": {
        "$eq": [
          "$$s.label",
          1
        ]
       }
      }
    }
  }
},
{
"$addFields": {
  "sales": {
    "$map": {
      "input": "$sales",
      "as": "s",
      "in": {
        "$mergeObjects": [
          "$$s",
          {
            codes: {
              "$filter": {
                "input": "$$s.codes",
                "as": "c",
                "cond": {
                  "$eq": [
                    "$$c.active",
                    true
                  ]
                }
              }
            },
            prices: {
              "$filter": {
                "input": "$$s.prices",
                "as": "p",
                "cond": {
                  "$gt": [
                    "$$p.valid_from",
                    1
                  ]
                }
              }
            }
          }
        ]
      }
    }
  }
 }
}
])

Playground

Explained:

1. Match the document with _id="abc"
2. addFields1 to filter only objects from sales[] having label=1
3. addFields2/map/mergeObjects to filter only the active codes[] and valid prices[] inside the sales[] array.

You can also combine the 2) and 3) in single nested addFields as follow here