Join multiple collections in MongoDB

Question

Greetings amigo i have one question related joining multiple collection in MongoDb i have collection schema something like below

Posts Collection
{
    "type": "POST_TYPE",    
    "_id": "63241dffb0f6770c23663230",
    "user_id": "63241dffb0f6770c23663230",
    "post_id": "63241dffb0f6770c23663230",
    "likes": 50
}

Post Types: 1. Event
{
    "date": "2022-09-16T07:07:18.242+00:00",    
    "_id": "63241dffb0f6770c23663230",
    "user_id": "63241dffb0f6770c23663230",
    "venue": "Some Place",
    "lat": "null",
    "long": "null",
}

Post Types: 2. Poll
{
    "created_date": "2022-09-16T07:07:18.242+00:00",    
    "_id": "63241dffb0f6770c23663230",
    "user_id": "63241dffb0f6770c23663230",
    "question": "Question??????",
    "poll_opt1": "Yes",
    "poll_opt2": "No",
    "poll_opt1_count": "5",
    "poll_opt2_count": "2"
}

now i have to join Post collection with respective collection e.g.

"post_id" to Event::_id or Poll::_id with condition to Post::type

i have tried aggregation but it does not gave expected output. i am trying to get output something like below

[
  {
    "type": "event",
    "_id": "63241dffb0f6770c23663230",
    "user_id": "63241dffb0f6770c23663230",
    "post_id": {
      "date": "2022-09-16T07:07:18.242+00:00",
      "_id": "63241dffb0f6770c23663230",
      "user_id": "63241dffb0f6770c23663230",
      "venue": "Some Place",
      "lat": "null",
      "long": "null"
    },
    "likes": 50
  },
  {
    "type": "poll",
    "_id": "63241dffb0f6770c23663230",
    "user_id": "63241dffb0f6770c23663230",
    "post_id": {
      "created_date": "2022-09-16T07:07:18.242+00:00",
      "_id": "63241dffb0f6770c23663230",
      "user_id": "63241dffb0f6770c23663230",
      "question": "Question??????",
      "poll_opt1": "Yes",
      "poll_opt2": "No",
      "poll_opt1_count": "5",
      "poll_opt2_count": "2"
    },
    "likes": 50
  }
]

is there any efficient way to achieve this or better MongoDb schema to manage these types of records?

Answer 1

You can try something like this, using $facet:

db.posts.aggregate([
  {
    "$facet": {
      "eventPosts": [
        {
          "$match": {
            type: "event"
          },
          
        },
        {
          "$lookup": {
            "from": "events",
            "localField": "post_id",
            "foreignField": "_id",
            "as": "post_id"
          }
        }
      ],
      "pollPosts": [
        {
          "$match": {
            type: "poll"
          },
          
        },
        {
          "$lookup": {
            "from": "poll",
            "localField": "post_id",
            "foreignField": "_id",
            "as": "post_id"
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "doc": {
        "$concatArrays": [
          "$pollPosts",
          "$eventPosts"
        ]
      }
    }
  },
  {
    "$unwind": "$doc"
  },
  {
    "$replaceRoot": {
      "newRoot": "$doc"
    }
  },
  {
    "$addFields": {
      "post_id": {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$size": "$post_id"
              },
              0
            ]
          },
          "then": {},
          "else": {
            "$arrayElemAt": [
              "$post_id",
              0
            ]
          }
        }
      }
    }
  }
])

We do the following, in the query:

1. Perform two $lookups for the different post_type within $facet. This unfortunately will increase, with the different values of post_type.

2. Then we combine all the arrays obtained from $facet, using $concatArray.

3. Then we unwind the concatenated array, and bring the nested document to the root using $replaceRoot.

4. Finally, for post_id we pick the first array element if it exists, to match the desired output.

Playground link.