How to join a collection inside an array of objects in MongoDB

Question

I have document like this in a collection called notifications

[
   {
       _id: 4f6a686ad4821115c0b4a721,
       title: "title 1"
       comments: [
         {
           comment: 'test 1',
           client_id: '1664158590921',
           createdAt: 2020-09-22T20:00:00.000Z
         },
         {
           comment: 'test 2',
           client_id: '1664158590875',
           createdAt: 2020-09-22T20:00:00.000Z
         }
       ]
   }
]

I have document like this in a collection called clients

{
    _id: 5f6a651faf591a3c742016dd,
    client_id: '1664158590921',
    email: '[email protected]'
}
{
    _id: 5f6a651faf591a3c742016dd,
    client_id: '1664158590875',
    email: '[email protected]'
}

I am trying to create a mongodb query to get a result like this:

[
    {
       _id: 4f6a686ad4821115c0b4a721,
       title: "title 1"
       comments: [
         {
             comment: 'test 1',
             client_id: {
                _id: 5f6a651faf591a3c742016dd,
                client_id: '1664158590921',
                email: '[email protected]'
             },
             createdAt: 2020-09-22T20:00:00.000Z
         },
         {
             comment: 'test 2',
             client_id: {
                _id: 5f6a651faf591a3c742016dd,
                client_id: '1664158590875',
                email: '[email protected]'
             },
             createdAt: 2020-09-22T20:00:00.000Z
         }
      ]
   }
]

Is it possible or not? Any advice on how to approach a similar result like this is much appreciated, thanks

Kh.Nomani · Accepted Answer · 2020-09-24 10:45:06Z

1

Since the localField is an array, first you have to $unwind it.(required for next stage)
use $lookup for matching.
Group them by _id and cleanup the output.

The solution should be something like this.

db.notifications.aggregate([
   {
      "$unwind":"$comments"
   },
   {
      "$lookup":{
         "from":"clients",
         "localField":"comments.client_id",
         "foreignField":"client_id",
         "as":"client_id"
      }
   },
   {
      "$group":{
         "_id":"$_id",
         "title":{
            "$first":"$title"
         },
         "comments":{
            "$push":{
               "comment":"$comments.comment",
               "client_id":"$client_id",
               "createdAt":"$comments.createdAt"
            }
         }
      }
   }
]).pretty()

answered Sep 24, 2020 at 10:45

Kh.Nomani

1852 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

How to join a collection inside an array of objects in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related