How to use multiple $lookup with arrays of objects

let’s consider i have some collection and the structures of data is look like these

post col:

{
    _id: ObjectId("5fce0e137ff7401634bad2ac")
    address: "new york"
    description: "this is a great city"
    image: "images\0.4644859674390589-gikiguk.jpg"
    userId: "5fcdcd8487e37216bc82171b"
}

user col:

{
    _id: ObjectId("5fcdcd8487e37216bc82171b")
    name: "jack"
    profile: {image: "images\0.4644859674390589-dofowfg.jpg", description: "be happy dude"}
    email: "test@test.com"
    password: "2a$12$gzRrY83QAjwLhdyvn3JQ2OcMj3XG65.BULva4cZqcuSxDhhMbSXCq"
}

likesComments col:

{
    _id: ObjectId("5fce0e191ff7301666kao2xc")
    likes: {quantity: 1, likes: [{isActive: true, userId: "5fcdcd8487c31216cc87886r"}]}
    comments: {quantity: 1, comments: [{userId: "5fcdcd8487c31216cc87886r" , comment: "awesome city"}]}
    postId: "5fce0e137ff7401634bad2ac"
}

what i want to do is i have a userId (maybe more than one) that i want to search through all of posts (that is belong to the user) and get some data from posts collection and then $lookup to users collection that again get some data about user and after that every post has own postId (i converted _id to postId ). with postId i can $lookup to likeComments documents and get some data about any posts, but if a post don’t have any likes or comments, all of my query that i wrote them gives me an empty array [] (not data at all just empty array) but if a post has at least one like and one comment, all of my query works find.

what i expect is something like this:

    [
      {
        postId: '5fce0e137ff7401634bad2ac',    //from post
        location: 'shiraz', // from post
        description: 'this is a greate city',    // from post
        image: 'images\\0.4644859674390589-gikiguk.jpg',    // from post
        userId: '5fcdcd8487e37216bc82171b',    // from user
        name: 'mohammad',    // from user
        profile: 'images\\0.6093033055735912-DSC_0002_2.JPG',    // from user
        comments: { quantity: 1, comments: [{userId: "...", name: "...", profile: "...", comment: "..."}] },    // from likesComments and then lookup to users documets to get data about users that wrote comments
        likes: {quantity: 1, [{userId: "...", name: "...", profile: "..."}]}    // from likesCommnets and then lookup to users documets to get data about users that liked the post
      }
    ]

query:

i know that my query is wrong but i can’t find what’s wrong here.

db.collection("posts")
          .aggregate([
            { $match: { userId: { $in: users.map(u => u) } } },
            {
              $project: {
                _id: 0,
                userId: { $toObjectId: "$userId" },
                postId: { $toString: "$_id" },
                location: "$address",
                description: "$description",
                image: "$image"
              }
            },
            {
              $lookup: {
                from: "users",
                localField: "userId",
                foreignField: "_id",
                as: "userInfo"
              }
            },
            { $unwind: "$userInfo" },
            {
              $project: {
                _id: 0,
                postId: 1,
                location: 1,
                description: 1,
                image: 1,
                userId: { $toString: "$userId" },
                name: "$userInfo.name",
                profile: "$userInfo.profile.image"
              }
            },
            {
              $lookup: {
                from: "likes-comments",
                localField: "postId",
                foreignField: "postId",
                as: "likesComments"
              }
            },
            { $unwind: "$likesComments" },
            {
              $project: {
                postId: 1,
                location: 1,
                description: 1,
                image: 1,
                userId: 1,
                name: 1,
                profile: 1,
                likes: {
                  $map: {
                    input: "$likesComments.likes.likes",
                    as: "item",
                    in: {
                      $toObjectId: "$item.userId"
                    }
                  }
                },
                quantity: "$likesComments.comments.quantity",
                comments: {
                  comments: {
                    $map: {
                      input: "$likesComments.comments.comments",
                      as: "item",
                      in: {
                        $toObjectId: "$item.userId"
                      }
                    }
                  }
                }
              }
            },
            {
              $lookup: {
                from: "users",
                localField: "likes",
                foreignField: "_id",
                as: "likes"
              }
            },
            { $unwind: "$likes" },
            {
              $lookup: {
                from: "users",
                localField: "comments.comments",
                foreignField: "_id",
                as: "comments"
              }
            },
            { $unwind: "$comments" },
            { $addFields: { quantity: "$quantity" } },
            {
              $project: {
                _id: 0,
                postId: 1,
                location: 1,
                description: 1,
                image: 1,
                userId: 1,
                name: 1,
                profile: 1,
                likes: [
                  {
                    userId: { $toString: "$likes._id" },
                    name: "$likes.name",
                    profile: "$likes.profile.image"
                  }
                ],
                comments: {
                  quantity: 1,
                  comments: [
                    {
                      userId: { $toString: "$comments._id" },
    

Hi @mohammad_nowresideh,

Welcome to MongoDB community!

The query is very complex and resembles more of a relationsional schema for relational databases. This examples exactly why your schema design is not optimal for MongoDB and will put you in lots of troubles and complex aggregation.

If your application expects to get and obejct like the following why not to structure the data this way:

{
        postId: '5fce0e137ff7401634bad2ac',    //from post
        location: 'shiraz', // from post
        description: 'this is a greate city',    // from post
        image: 'images\\0.4644859674390589-gikiguk.jpg',    // from post
        userId: '5fcdcd8487e37216bc82171b',    // from user
        name: 'mohammad',    // from user
        profile: 'images\\0.6093033055735912-DSC_0002_2.JPG',    // from user
        comments: { quantity: 1, comments: [{userId: "...", name: "...", profile: "...", comment: "..."}] },    // from likesComments and then lookup to users documets to get data about users that wrote comments
        likes: {quantity: 1, [{userId: "...", name: "...", profile: "..."}]}    // from likesCommnets and then lookup to users documets to get data about users that liked the post
      }

Whenever a user writes a post add all data to that document. Update likes and push comments into the arrays. If the comments array grows big add an extended document holding the rest of the comments.

You can index the userid field for searches.

Fixing this aggregation now will not avoid you from running into issues in the future, fixing the schema will.

Thanks,
Pavel

Thanks @Pavel_Duchovny,
Yes, you’r right this is very complex, but to have clear data i separated the additional information to another collection. in the next project i’ll do all of that in the one document.
in my current project if i want to change my schema i have to change a lot of code and this is very cumbersom. but may i ask you what is the query of this data?
please.

Hi @mohammad_nowresideh,

Not sure I understand the question.

If you cannot change the schema I would do 3 indexed queries rather than managing this very long 3 lookup query with all the transformation.

Query posts, query users, query comments.

Thanks
Pavel

1 Like

Thank you for answering my question @Pavel_Duchovny