How to use graph lookup aggregation in a embedded

2020-08-20 11:33发布

问题:

Here is my sample document from NetworkInfo collection.

{
"_id" : ObjectId("5a37595bd2d9ce37f86d612e"),
"edgeList" : [ 
    {
        "networkSource" : {
            "sourceId" : "pump1"
        },
        "networkRelationship" : {},
        "networkTarget" : {
            "targetId" : "chiller1",
            "parentId" : "pump1"
        }
    }, 
    {
        "networkSource" : {
            "sourceId" : "chiller1"
        },
        "networkRelationship" : {},
        "networkTarget" : {
            "targetId" : "secondaryPump1",
            "parentId" : "chiller1"
        }
    }, 
    {
        "networkSource" : {
            "sourceId" : "secondaryPump1"
        },
        "networkRelationship" : {},
        "networkTarget" : {
            "targetId" : "ahu1",
            "parentId" : "secondaryPump1"
        }
    }
]

}

I tried to create a graph lookup for the above document using the below code:

pump1->chiller1->secondary pump1->ahu1

db.getCollection("NetworkInfo").aggregate([ {$project:{_id:0}},{ $unwind : "$edgeList" }, { $out : "FlattenedNetwork" } ])
db.FlattenedNetwork.aggregate( [
{
  $graphLookup: {
     from: "FlattenedNetwork",
     startWith: "$edgeList.networkTarget.parentId",
     connectFromField: "edgeList.networkTarget.parentId",
     connectToField: "edgeList.networkTarget.targetId",
     as: "reportingHierarchy"
  }}])

This works fine. But, I wish to avoid using the temporary collection "FlattenedNetwork". I tried adding multiple aggregation functions but it didn't help.

回答1:

I made several tries but I did not find a real solution to this. I also watched the Webinar and this case is not covered. For this reason I decided to put a bounty on this question, with the hope that someone else could share a solution better than mine. However, the only way out (in my opinion) is using a view, that I declared like this:

db.createView("unwounded_docs", "NetworkInfo", [ 
        {
            $unwind : "$edgeList"
        }, 
        {
            $replaceRoot : {
                newRoot : "$edgeList"
            }
        }, 
        {
            $project : {
                "networkTarget" : 1
            }
        },
        {
            $addFields: {
                "_id": "$networkTarget.targetId"
            }
        }
    ]
);

I removed all useless fields just for clarity. The view will have this output:

{
    "networkTarget" : {
        "targetId" : "chiller1",
        "parentId" : "pump1"
    },
    "_id" : "chiller1"
},
{
    "networkTarget" : {
        "targetId" : "secondaryPump1",
        "parentId" : "chiller1"
    },
    "_id" : "secondaryPump1"
},
{
    "networkTarget" : {
        "targetId" : "ahu1",
        "parentId" : "secondaryPump1"
    },
    "_id" : "ahu1"
}

Since you can refer to a view in the from field of the $graphLookup stage, this is the pipeline (at least shorter than before):

db.unwounded_docs.aggregate( [
{
  $graphLookup: {
     from: "unwounded_docs",
     startWith: "$networkTarget.parentId",
     connectFromField: "networkTarget.parentId",
     connectToField: "networkTarget.targetId",
     as: "reportingHierarchy"
  }
}])