Suppose I have this dataset:

    [
        {
            _id: '1',
            name: 'test1',
            children: ['test2', so on...],
            status: 'enabled',
            version: 1,
        },
        {
            _id: '2',
            name: 'test2',
            children: [],
            status: 'enabled',
            version: 1,
        },
        {
            _id: '3',
            name: 'test2',
            children: ['test3'],
            status: 'enabled',
            version: 2,
        },
        {
            _id: '4',
            name: 'test2',
            children: [],
            status: 'disabled',
            version: 3,
        },
        {
            _id: '5',
            name: 'test3',
            children: [],
            status: 'enabled',
            version: 1,
        }
    ]

What I need to do is to get the children of _id=1 item and return the enabled ones and highest version.

Is there a way for me to do it plainly via aggregation stages or do I have to do it manually by code?

Current aggregation query that I have:

    db.Collection.aggregate([
        {
            $match: { name: 'test1', status: 'enabled' }
        },
        {
            $graphLookup: {
                from: "Collection",
                startWith: "$children",
                connectFromField: "children",
                connectToField: "name",
                as: "spreadChildren",
                restrictSearchWithMatch: {
                    status: 'enabled'
                }
            }
        }
    ])

This is the expected result:

    [
      {
        name: 'test1',
        children: ['test2'],
        status: 'enabled',
        version: 1,
        spreadChildren: [
          {
            _id: '3',
            name: 'test2',
            children: [],
            status: 'enabled',
            version: 2,
          },
        ]
      }
    ]

CodePudding user response：

You can do the followings in aggregation pipeline:

1. $unwind the the $graphLookup result.
2. $sort by version
3. $group at spreadChildren.name level and persist all fields; use $first to keep the largest version element
4. $group again on _id level to revert back to expected output

db.collection.aggregate([
  {
    $match: {
      name: "test1",
      status: "enabled"
    }
  },
  {
    $graphLookup: {
      from: "collection",
      startWith: "$children",
      connectFromField: "children",
      connectToField: "name",
      as: "spreadChildren",
      restrictSearchWithMatch: {
        status: "enabled"
      }
    }
  },
  {
    "$unwind": "$spreadChildren"
  },
  {
    $sort: {
      "spreadChildren.version": -1
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        name: "$spreadChildren.name"
      },
      children: {
        $first: "$children"
      },
      name: {
        $first: "$name"
      },
      spreadChildren: {
        $first: "$spreadChildren"
      },
      status: {
        $first: "$status"
      },
      version: {
        $first: "$version"
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      children: {
        $first: "$children"
      },
      name: {
        $first: "$name"
      },
      spreadChildren: {
        $push: "$spreadChildren"
      },
      status: {
        $first: "$status"
      },
      version: {
        $first: "$version"
      }
    }
  }
])

Here is the Mongo playround for your reference.