MongoDB: finding matched elements in the third level embed documents

Question

I am new to MongoDB. I have a typical use case. Here is my single document JSON structure.

{
    "category":"cat_1",
    "_id": "id1",
    "levels":[
        {
            "id":"l1",
            "orders":[
                {
                    "id":"o1",
                    "screens":[
                        {
                            "id":"l1o1s1",
                            "name":"screen1"
                        },
                        {
                            "id": "l1o1s2",
                            "name": "screen2"
                        }
                    ]
                },
                {
                    "id": "o2",
                    "screens": [
                        {
                            "id": "l1o2s1",
                            "name": "screen3"
                        },
                        {
                            "id": "l1o2s2",
                            "name": "screen4"
                        }
                    ]
                }
            ]
        },
        {
            "id": "l2",
            "orders": [
                {
                    "id": "o1",
                    "screens": [
                        {
                            "id": "l2o1s1",
                            "name": "screen5"
                        },
                        {
                            "id": "l2o1s2",
                            "name": "screen6"
                        }
                    ]
                },
                {
                    "id": "o2",
                    "screens": [
                        {
                            "id": "l2o2s1",
                            "name": "screen7"
                        },
                        {
                            "id": "l2o2s2",
                            "name": "screen8"
                        }
                    ]
                }
            ]
        }
    ]
}

Here I want to get the data of only given screen ids. For example, If ["l1o1s1","l1o2s2","l2o1s1","l2o2s1"] is my list of screen ids should find in the document, then I want the result as below (It should return the screen id's only given in the input)

 {
    "category":"cat_1",
    "_id": "id1",
    "levels":[
        {
            "id":"l1",
            "orders":[
                {
                    "id":"o1",
                    "screens":[
                        {
                            "id":"l1o1s1",
                            "name":"screen1"
                        }
                    ]
                },
                {
                    "id": "o2",
                    "screens": [
                        {
                            "id": "l1o2s2",
                            "name": "screen4"
                        }
                    ]
                }
            ]
        },
        {
            "id": "l2",
            "orders": [
                {
                    "id": "o1",
                    "screens": [
                        {
                            "id": "l2o1s1",
                            "name": "screen5"
                        },
                    ]
                },
                {
                    "id": "o2",
                    "screens": [
                        {
                            "id": "l2o2s1",
                            "name": "screen7"
                        }
                    ]
                }
            ]
        }
    ]
}

the inputs of screens need not be only in a single document. It may exist in the other categories too. then It should return that documents only with the mentioned screen ids.

any idea to retrieve the data like this?

Answer 1

As @alex-blex suggested, your schema doesnt support that kind of queries, but if you still want to achieve your result, you can try with aggregation pipeline, but might not be efficient.

You can try below aggregation pipeline:

db.collection.aggregate([
  {
    "$match": {
      "levels.orders.screens.id": {
        $in: [
          "l1o1s1",
          "l1o2s2",
          "l2o1s1",
          "l2o2s1"
        ]
      }
    }
  },
  {
    "$unwind": "$levels"
  },
  {
    "$unwind": "$levels.orders"
  },
  {
    "$unwind": "$levels.orders.screens"
  },
  {
    "$match": {
      "levels.orders.screens.id": {
        $in: [
          "l1o1s1",
          "l1o2s2",
          "l2o1s1",
          "l2o2s1"
        ]
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        _id: "$_id",
        "category": "$category",
        "levelId": "$levels.id",
        "orderId": "$levels.orders.id",
        "screens": "$levels.orders.screens"
      }
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        "levelId": "$levelId",
        "orderId": "$orderId",
        
      },
      "category": {
        $first: "$category"
      },
      "orderId": {
        $first: "$orderId"
      },
      "levelId": {
        $first: "$levelId"
      },
      "screens": {
        $push: "$screens"
      }
    }
  },
  {
    $project: {
      _id: "$_id._id",
      levelId: "$levelId",
      category: "category",
      "orders": {
        id: "$orderId",
        screens: "$screens"
      }
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        "levelId": "$levelId",
        
      },
      "category": {
        $first: "$category"
      },
      "levelId": {
        $first: "$levelId"
      },
      "orders": {
        $push: "$orders"
      }
    }
  },
  {
    $project: {
      _id: "$_id._id",
      category: "category",
      "levels": {
        id: "$levelId",
        orders: "$orders"
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      "category": {
        $first: "$category"
      },
      "levels": {
        $push: "$levels"
      }
    }
  },
  
])

This is a really large and complex aggregation pipeline, as a result performance and efficiency is not gurranteed, but you can check if this works for you.

Explanation:

1. $match to filter out your desired set of documents only for later stages of aggregation pipeline.

2. $unwind to unwind the levels array

3. $unwind to unwind the orders array

4. $unwind to unwind the screens array

5. $match to get the exact screens which is required

6. $replaceRoot to re-structre the JSON document, so that we can group back the unwinded arrays

7. $group to group the screens to screens array

8. $project to again restructure the resultant documents, to get out original structure back

9. $group to group the orders to orders array

10. $project to again restructure the resultant documents, to get out original structure back

11. $group to group the levels to levels array and get back our final result

You can check the results on this Mongo Playground

Alternatively, You can run the agreation pipeline up until stage 5, and then combine the result back into the format you want in your backend code.