MongoDB aggregation: flatten array property into root array

Question

I'm working on a feature that recursively looks up a thread of comments using $graphLookUp, and I almost have it. (albeit in a somewhat convoluted way but it is working!)

The last step I need is the following: instead of having the nested posteriorThread as a property of the root array ($$ROOT), just merge it onto the root itself.

AGGREGATION CODE:

const posteriorThread = await Comment.aggregate([
  {
    $match: {
      _id: post.threadDescendant
    }
  },

  {
    $graphLookup: {
      from: 'baseposts',
      startWith: '$threadDescendant',
      connectFromField: 'threadDescendant',
      connectToField: '_id',
      as: 'posteriorThread'
    }
  },

  {
    $unwind: '$posteriorThread'
  },

  {
    $sort: { 'posteriorThread.depth': 1 }
  },

  {
    $group: {
      _id: '$_id',
      posteriorThread: { $push: '$posteriorThread' },
      root: { $first: '$$ROOT' }
    }
  },

  {
    $project: {
      'root.posteriorThread': 0
    }
  },

  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            posteriorThread: '$posteriorThread'
          },
          '$root'
        ]
      }
    }
  }
]);

CURRENT OUTPUT

 OUTPUT: posteriorThread
[
  {
    _id: '5f7eab40575e6fc56ee07604',
    onModel: 'BasePost',
    depth: 1,
    user: '5f5da45245c07cc06e51b09f',
    text: 'thread 0',
    isThread: true,
    threadDescendant: '5f7eabad575e6fc56ee07607',
    posteriorThread: [
      {
        _id: '5f7eabad575e6fc56ee07607',
        onModel: 'Comment',
        depth: 2,
        user: '5f5da45245c07cc06e51b09f',
        text: 'thread 1',
        isThread: true,
        threadDescendant: '5f7eac82575e6fc56ee07609'
      },
      {
        _id: '5f7eac82575e6fc56ee07609',
        onModel: 'Comment',
        depth: 3,
        user: '5f5da45245c07cc06e51b09f',
        text: 'thread 2',
        isThread: true
      }
    ]
  }
];

DESIRED OUTPUT

OUTPUT: posteriorThread 
[
  {
    _id: '5f7eab40575e6fc56ee07604',
    onModel: 'BasePost',
    depth: 1,
    user: '5f5da45245c07cc06e51b09f',
    text: 'thread 0',
    isThread: true,
    threadDescendant: '5f7eabad575e6fc56ee07607'
  },
  {
    _id: '5f7eabad575e6fc56ee07607',
    onModel: 'Comment',
    depth: 2,
    user: '5f5da45245c07cc06e51b09f',
    text: 'thread 1',
    isThread: true,
    threadDescendant: '5f7eac82575e6fc56ee07609'
  },
  {
    _id: '5f7eac82575e6fc56ee07609',
    onModel: 'Comment',
    depth: 3,
    user: '5f5da45245c07cc06e51b09f',
    text: 'thread 2',
    isThread: true
  }
];

I could accomplish this after the aggregation in regular js, but I would prefer to do it all in the aggregation. The part that needs to be replaced is the mergeObjects bit and replaced with something else or the group aggregation and taking a different strategy, but I'm not sure what to put in it's place. Also, if you have any other suggestions to make this cleaner, I'm all ears.

Thanks in advance.

Sunil K Samanta · Accepted Answer · 2020-10-10 12:40:18Z

6

Its really challenging. Atleast for me. And really very interesting case. Lets try my solution. Hope it works..

db.test.aggregate([

    // PREVIOSU STEPS YOU ALREADY DID

    {
        $group: {
            _id: "$_id",
            items: {$push: "$$ROOT"},
            subItems: {$first: "$posteriorThread"}
        }
    },
    {
        $project: {
            "items.posteriorThread": 0
        }
    },
    {
        $addFields: {
            allItems: {
                $concatArrays: ["$items", "$subItems"]
            }
        }
    },
    {
        $group: {
            _id: null,
            mergedItems: {$push: "$allItems"}
        }
    },
    {
        $unwind: "$mergedItems"
    },
    {
        $unwind: "$mergedItems"
    },
    {
        $replaceRoot: {
          newRoot: "$mergedItems"
        }
    }

])

edited Oct 10, 2020 at 12:40

answered Oct 10, 2020 at 7:41

Sunil K Samanta

1711 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ram Over a year ago

Hey, your solution did not work because subItems is not an array and therefore could not be concatenated, but it did get me closer! I basically have it now, but the $replaceRoot is still leaving me with an nested array, but it's not a big deal as I can just get the correct solution after the aggregation.

ram Over a year ago

Check out my answer if you want to see my final solution.

Sunil K Samanta Over a year ago

Nice to hear that you got your solution. I don't know why my solution didn't work. I actually worked on your CURRENT OUTPUT. That means you have to use your steps you already did and then add my pipeline stages after your existing stages. In your CURRENT OUTPUT the posteriorThread is an array of items. So it sould have worked. anyway I actually stored your CURRENT OUTPUT into a collection and then I tried my steps. It gave me the results. Anyway the problem was really interesting..

ram Over a year ago

Hmm, that's strange. Yeah, subItems is using $first which returns an object, so when I went to $concat it was throwing and error, something like 'Cannot concat and object'. But yeah, I thought adding a simple thread feature would be a few lines and it turned into a whole day endeavor of aggregation and bugs! Thanks again for your help; it really set me on the right path. If you want to copy and paste my answer, I'll give you the credit for the answer.

Sunil K Samanta Over a year ago

Haha.. No no. Not needed. Please. I'm glad it really helped! And thank you for asking. And honestly I had no idea how I'd do that, it was experimental for me too. The problem statement was interesting. So I started working on that and I also got some lessons from it.

ram · Accepted Answer · 2020-10-10 22:11:10Z

1

Thanks to @Sunil K Samanta for steering me in the right direction. It's not the prettiest solution, but it does give me the right solution.

const posteriorThread = await Comment.aggregate([
  {
    $match: {
      _id: post.threadDescendant
    }
  },

  {
    $graphLookup: {
      from: 'baseposts',
      startWith: '$threadDescendant',
      connectFromField: 'threadDescendant',
      connectToField: '_id',
      as: 'posteriorThread'
    }
  },

  {
    $unwind: '$posteriorThread'
  },

  {
    $sort: { 'posteriorThread.depth': 1 }
  },

  {
    $group: {
      _id: '$_id',
      items: { $push: '$$ROOT.posteriorThread' },
      root: { $push: '$$ROOT' },
    },
  },

  {
    $project: {
      items: 1,
      root: { $slice: ['$$ROOT.root', 0, 1] },
    },
  },

  {
    $project: {
      'root.posteriorThread': 0,
    },
  },

  {
    $addFields: {
      allItems: {
        $concatArrays: ['$root', '$items'],
      },
    },
  },

  {
    $replaceRoot: {
      newRoot: {
        full_posterior: '$$ROOT.allItems',
      },
    },
  },
])
)[0].full_posterior;

answered Oct 10, 2020 at 22:11

ram

6905 silver badges17 bronze badges

1 Comment

Fasid Mpm Over a year ago

[0].full_posterior is what we are trying to avoid in the first place

Collectives™ on Stack Overflow

MongoDB aggregation: flatten array property into root array

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related