Suppose I have documents in bookUser collection as:

{
    'authored_by':ObjectId('asdfs'),
    'general':{'address':{'city':'XYZ'}}
}

{
    'authored_by':ObjectId('jkfo21'),
    'general':{'address':{'city':'YYZ'}}
}

and another collection center_data as:

{
    'authored_by': ObjectId('asdfs'),
    'books':{'name':'alpha'},
    'book_type':'adventure'
    'general':{'address':{'city':'ABC'}}
}

{
    'authored_by': ObjectId('jkfo21'),
    'books':{'name':'sigma'},
    'book_type':'drama'
    'general':{}
}

I want to fetch all the objects and get the details from the center_data collection:

Sample O/P:

[
    {
        'authored_by':ObjectId('asdfs'),
        'general':{'address':{'city':'XYZ'}},
        'books':[
            {
               'address': 'ABC'
            }
        ]
    },
    {
        'authored_by':ObjectId('jkfo21'),
        'general':{'address':{'city':'YYZ'}},
        'books':[
            {
               'address': ''
            }
        ]
    }
]

For this I tried as::

db.bookUser.aggregate([
  {
    $lookup: {
      from: "center_data",
      localField: "_id",
      foreignField: "authored_by",
      as: "books"
    }
  },
  {
    $project: {
      books: {
        $filter: {
          input: "$books",
          as: "book",
          cond: {
            $and: [
              {
                $eq: [
                  "book_type",
                  "adventure"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

But on the query, it gives an empty array. I want to all the documents from bookUser collection and get the address from center_data collection and if book_type is not adventure populate address as empty.

Please let me know if anyone needs any further information.

CodePudding user response：

Concerns

1. There is no _id field in the document of the bookUser collection. Acoording to your provided data, the localField should be authored_by.

2. From the statement below, you are comparing the word "book_type" and "adventure" which never be same.

$and:[
  {
    $eq: ['book_type','adventure']
  }
]

You need to use $$book.book_type to access the variable value.

Solution

db.bookUser.aggregate([
  {
    $lookup: {
      from: "center_data",
      localField: "authored_by",
      foreignField: "authored_by",
      as: "books"
    }
  },
  {
    $project: {
      authored_by: 1,
      general: 1,
      books: {
        $filter: {
          input: "$books",
          as: "book",
          cond: {
            $and: [
              {
                $eq: [
                  "$$book.book_type",
                  "adventure"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground