$match array element when array nested in another array in aggregate

Question

I have 4 collections: categories, filters, options, products. And I want to get all filters of category and filter options for those filters that belong to the category.

For your convenience here is the demo: https://mongoplayground.net/p/0DB6gKq1fSx

Collection categories:

[
  {
    "_id": 1,
    "title": "11 pro max",
    "filters": [
      10
    ],
  },    
]

Collection filters:

[
  {
    "_id": 10,
    "filterType": "checkbox",
    "title": "Year",
    "targetProperty": "year",
    "options": [
      100,
      101
    ]
  }
]

Collection options:

[
  {
    "_id": 100,
    "value": 2019,
    "products": [
      1000
    ]
  },
  {
    "_id": 101,
    "value": 2020,
    "products": [
      1001
    ]
  }
]

Collection products:

[
  {
    "_id": 1000,
    "name": "iPhone 11 Pro Max",
    "categories": [
      1
    ]
  },
  {
    "_id": 1001,
    "name": "iPhone 12 Pro Max",
    "categories": [
      2
    ]
  }
]

My query:

db.categories.aggregate([
  {
  // on this stage there may be any kind of query, but for simplicity I left it like this
    "$match": {
      _id: 1
    }
  },
  {
    "$lookup": {
      "from": "filters",
      "localField": "filters",
      "foreignField": "_id",
      "as": "filters",
      "let": {
        "category_id": "$_id"
      },
      "pipeline": [
        {
          "$lookup": {
            "from": "options",
            "localField": "options",
            "foreignField": "_id",
            "as": "options",
            "pipeline": [
              {
                $lookup: {
                  "from": "products",
                  "localField": "products",
                  "foreignField": "_id",
                  "as": "products"
                }
              },
              {
                $match: {
                  $expr: {
                    $in: [
                      "$$category_id",
                      "$products.categories"
                    ]
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
])

For some reason I get as the result:

[
  {
    "_id": 1,
    "filters": [
      {
        "_id": 10,
        "filterType": "checkbox",
        "options": [],
        "targetProperty": "year",
        "title": "Year"
      }
    ],
    "title": "11 pro max"
  }
]

I guess the issue is that on this stage products is array itself:

{
  $match: {
    $expr: {
      $in: [
        "$$category_id",
        "$products.categories"
      ]
    }
  }
}

But how to achieve the output I expected:

[
  {
    "_id": 1,
    "filters": [
      {
        "_id": 10,
        "filterType": "checkbox",
        "options": [
          {
            "_id": 100,
            "products": [
              {
                "_id": 1000,
                "categories": [
                  1
                ],
                "name": "iPhone 11 Pro Max"
              }
            ],
            "value": 2019
          }
        ],
        "targetProperty": "year",
        "title": "Year"
      }
    ],
    "title": "11 pro max"
  }
]

Please note that I need to use "$$category_id" variable to be able to handle any kind of query on first outermost $match stage.

Thanks for suggestions.

Answer 1

You need to add $unwind just after products's $lookup since products store array of arrays
[ [category1, ...] ]

{
    $lookup: {
        "from": "products",
        "localField": "products",
        "foreignField": "_id",
        "as": "products"
    }
},
{
    $unwind: "$products"
},
{
    $match: {
        $expr: {
            $in: [
                "$$category_id",
                "$products.categories"
            ]
        }
    }
}

MongoPlayground

Edit: If you don't want to lose the shape of your object:

1. Add new field tmp with $products values
2. Unwind tmp
3. Match $$category_id with tmp
4. Remove tmp variable