How to get parent and parent's siblings in a MongoDB request

user_12953291 0
Viewed 1097

In a MongoDB/NodeJS project, I've ordered elements of my collection like a family tree. Within a particular route, I'm trying to get the parent of an element, but also parent's siblings, with a given depth as shown in the image below :

Collection tree, items wanted through the request

In the collection, for each item, I store, among other data :

  • parentId
  • grandParentId
  • isRoot (boolean)

I've tried to do something with GraphLookup, basing my request on linking parentId to grandParentId, like this :

db.arguments.aggregate([
    {$match: { _id: mongoose.Types.ObjectId(id) }},
    ,
    {$graphLookup: {
        from: 'arguments',
        startWith: '$grandParentId',
        connectFromField: 'grandParentId',
        connectToField: 'parentId',
        maxDepth: Number(parentsDepth),
        as: 'parentsHierarchy',
        depthField: 'depth',
        restrictSearchWithMatch: { isDeleted: false }
    }}
])

It works pretty well, but the problem is that it cannot retrieve root element, which have no parentId. I've thought about doing two separate views containing each one a GraphLookup (one based on parentId/grandParentId, the other on id/parentId), then merging both views while deleting duplicates, but it looks weird to perform two potentially big requests in order to get just the root element.

I would like to find a reliable solution as I plan to allow an item to have multiple parents.

node.js mongodb graph
1 Answers

You can change it to be in multiple steps:

  1. Find the array of direct ascendents (parent, grand-parent, etc.)
  2. Find immediate descendents of each grand-parent and "higher" (thus giving you parent siblings, grand-parent siblings etc.)
  3. Merge the two arrays into a single set (thus ensuring uniqueness)
db.arguments.aggregate([
    {$match: { _id: mongoose.Types.ObjectId(id) }},
    {$graphLookup: {
        from: 'arguments',
        startWith: '$parentId',
        connectFromField: 'parentId',
        connectToField: '_id',
        maxDepth: Number(parentsDepth),
        as: 'parentsHierarchy',
        depthField: 'depth',
        restrictSearchWithMatch: { isDeleted: false }
    }},
    {$unwind: "$parentsHierarchy"},
    {$lookup: {
        from: 'arguments',
        let: { id: '$parentsHierarchy._id', depth: '$parentsHierarchy.depth' },
        pipeline: [
        {$match:{$expr:{
            $and: [{
                $eq: ['$$id', '$parentId']
            },{
                $gte: ["$$depth", 2]
            }]
        }}},
        {$addFields:{
            depth: {$sum: ["$$depth", -1]}
        }}],
        as: 'children'
    }},
    {$group:{
        _id: "$_id",
        parentsHierarchy: {$addToSet: "$parentsHierarchy"},
        children: {$push: "$children"}
        // The rest of your root fields will have to be added here (someField: {$first: "$someField"})
    }},
    {$addFields:{
        hierarchy: {$setUnion: ["$children", "$parentsHierarchy"]}
    }}
])

See How to push multiple columns' value within group regarding the $setUnion.

Original Answer:

If you are only interested in the parent and the parent's siblings, you can use a $lookup stage instead of $graphLookup, since you don't need the recursion the graph gives you.

Your $lookup could be done like this:

db.test.aggregate([
    {$lookup: {
        from: 'arguments',
        let: { parentId: '$parentId', grandParentId: '$grandParentId' },
        pipeline: [{$match:{$expr:{
            $or: [{
                $eq: ['$_id', '$$parentId']
            },{
                $eq: [{$ifNull: ['$parentId', 'xyz']}, '$$grandParentId']
            }]
        }}}],
        as: 'parentsAndTheirSiblings'
    }}
])

This way your root element should still be found by the first part of the $match in the pipeline.

Note that I am using $ifNull in the second part to filter out "root" elements, since the $parentId and $$grandparentId will be null or undefined when looking for an element at depth 1. If the intended behaviour is that all root elements should be found for any depth 1 element (if root elements are considered siblings), then you can get rid of it and simply compare $parentId and $$grandparentId straight up.

Docs for lookup: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

user_10865450
Related