Return only matched sub-document elements within a nested array of references

Question

I have a collection named Container with array of embedded documents called embeddedMany. Each embedded document is referencing several other documents named Referenced and these references are stored in array called referenceMany. The document looks like this.

{ 
    "_id" : ObjectId("5a312337ea5cb32d30005d25"), 
    "embeddedMany" : [
        {
            "referencedMany" : [
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d24"), "myDb"),
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d23"), "myDb")
            ]
        }, 
        {
            "referencedMany" : [
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d23"), "myDb")
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
            ]
        },
        {
            "referencedMany" : [
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d24"), "myDb")
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
            ]
        }
    ], 
}

Now I need to find all embedded documents that reference certain document. Let's say this one: DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb").

I need the resulting document to look like this:

{ 
    "_id" : ObjectId("5a312337ea5cb32d30005d25"), 
    "embeddedMany" : [
        {
            "referencedMany" : [
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d23"), "myDb")
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
            ]
        },
        {
            "referencedMany" : [
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d24"), "myDb")
                DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
            ]
        }
    ], 
}

I read this very similar problem. So I guess I need to use aggregation and filter the embeddedMany field. My aggregation looks like this so far:

db.Container.aggregate(
    [
        {
            $match: {
                "embeddedMany.referencedMany.$id": ObjectId("5a312337ea5cb32d30005d22")
            }
        },
        {
            $project: {
                "embeddedMany": {
                    "$filter": {
                        "input": "$embeddedMany",
                        "as": "embedded",
                        "cond": {
                            "$eq": [
                                "$$embedded.referencedMany.$id",
                                ObjectId("5a312337ea5cb32d30005d22")
                            ]
                        }
                    }
                }
            }
        },
    ]
);

And this is where I hit the wall. Because the MongoDB has the outstanding bug that prevents me from comparing $id in the $eq expression. There are some mentions about using $objectToArray as hack but I couldn't pull it off all together.

Any help would be highly appreciated.

s7vr · Answer 1 · 2017-12-15T14:56:03.373

You can use the below $project stage for comparing dbref ids.

$objectToArray to convert DBRef into key value pairs.

Next step is the $filter the dbref key value pairs to only contain id key value pairs.

Next step to $let + $map expression to project only id values.

Final step is to $filter "embeddedMany" array by comparing passed in ObjectId values against the referencedMany id values using $in expression.

{"$project":{"embeddedMany":{
  "$filter":{
    "input":"$embeddedMany",
    "as":"embedded",
    "cond":{
      "$in":[
        ObjectId("5a312337ea5cb32d30005d22"),
        {
          "$map":{
            "input":"$$embedded.referencedMany",
            "as":"referenced",
            "in":{
              "$arrayElemAt":[
                {
                  "$let":{
                    "vars":{
                      "id":{
                        "$filter":{
                          "input":{"$objectToArray":"$$referenced"},
                          "as":"r",
                          "cond":{"$eq":["$$r.k",{"$literal":"$id"}]}
                        }
                      }
                    },
                    "in":"$$id.v"
                  }
                },
                0
              ]
            }
          }
        }
      ]
    }
  }
 }
}}

OMG, this is complicated. Thanks so very much for helping out. I will need to delve into it before I can understand it. — Josef Sábl, Dec 14 '17 at 09:14
My apologies. I've made it unnecessarily complicated. Refactored now. — s7vr, Dec 15 '17 at 14:57

score 1 · Answer 2 · answered Feb 13 '18 at 19:51

You don't need to break down the objects nor worry about the bug you referenced if the result you want is like the example you gave. Simply use comparisons between DBRef objects in your pipeline (example done in mongo shell:

mydbref = DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb");
db.dbref.aggregate([
   {$match:{"embeddedMany.referencedMany":mydbref}}, 
   {"$project":{"embeddedMany":{
     "$filter":{
       "input":"$embeddedMany",
       "cond":{
          "$in":[{$literal:mydbref},"$$this.referencedMany"]
       }
     }
   }}}
])
{
 "_id" : ObjectId("5a312337ea5cb32d30005d25"),
 "embeddedMany" : [
    {
        "referencedMany" : [
            DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d23"), "myDb"),
            DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
        ]
    },
    {
        "referencedMany" : [
            DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d24"), "myDb"),
            DBRef("Referenced", ObjectId("5a312337ea5cb32d30005d22"), "myDb")
        ]
    }
 ]
}

Return only matched sub-document elements within a nested array of references

2 Answers2