I have a mongodb document like this

{
  "_id": {
    "$oid": "6241dd90891458501c17d627"
  },
  "A": [
    {
      "_id": {
        "$oid": "6241ddb1891458501c17d63e"
      },
      "B": [
        {
          "_id": {
            "$oid": "6241ddc4891458501c17d674"
          }
        },
        {
          "_id": {
            "$oid": "6241ddda891458501c17d675"
          }
        }
      ]
    },
    {
      "_id": {
        "$oid": "6241ddbe891458501c17d63f"
      },
      "B": [
        {
          "_id": {
            "$oid": "6241ddda891458501c17d678"
          }
        },
        {
          "_id": {
            "$oid": "6241ddda891458501c17d679"
          }
        }
      ]
    }
  ]
}

This document has 2 nested arrays: an array of "A" elements, inside each element of "A" there's an array of "B" elements. I need to search by an _id of a "B" element, let's say 6241ddda891458501c17d679. I need a way to obtain this structure in mongodb

{
  "_id": {
    "$oid": "6241dd90891458501c17d627"
  },
  "A": [
    {
      "_id": {
        "$oid": "6241ddbe891458501c17d63f"
      },
      "B": [
        {
          "_id": {
            "$oid": "6241ddda891458501c17d679"
          }
        }
      ]
    }
  ]
}

How can I achieve this? Thanks very much

CodePudding user response：

Maybe something like this:

Option 1, Find:

db.collection.find({
 "A.B._id": {
   "$oid": "6241ddda891458501c17d679"
 }
},
{
 "A": {
   "$filter": {
     "input": {
      "$map": {
         "input": "$A",
         "as": "a",
         "in": {
           "_id": "$$a._id",
           "B": {
             "$filter": {
               "input": "$$a.B",
               "as": "b",
               "cond": {
                 "$eq": [
                   {
                     "$oid": "6241ddda891458501c17d679"
                   },
                   "$$b._id"
                 ]
               }
             }
           }
         }
       }
     },
     "as": "an",
     "cond": {
       "$ne": [
         "$$an.B",
         []
       ]
     }
   }
 }
})

Explained:

1. Use find() with match query on "A.B._id" ( good to have index on this filed for best performance)
2. In the filter part add $filter/map/filter combination to filter only the matching _id for array B elements and preserve the array A _id , also in the initial filter condition use only non-empty arrays [] to avoid having elements from empty arrays in the final result.

playground1

Option 2 , aggregation:

 db.collection.aggregate([
 {
   $match: {
    "A.B._id": {
      "$oid": "6241ddda891458501c17d679"
    }
  }
 },
 {
  "$addFields": {
    "A": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$A",
            "as": "a",
            "in": {
              "_id": "$$a._id",
              "B": {
                "$filter": {
                  "input": "$$a.B",
                  "as": "b",
                  "cond": {
                    "$eq": [
                      {
                        "$oid": "6241ddda891458501c17d679"
                      },
                      "$$b._id"
                    ]
                  }
                }
              }
            }
          }
        },
        "as": "an",
        "cond": {
          "$ne": [
            "$$an.B",
            []
          ]
        }
      }
    }
  }
}
])

playground2