CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
cjmling
cjmling

Reputation: 7278

Mongo lookup to return local even when foreign not matched

Let say i have User collection and a user can have many Books.

I want to query User and then lookup to get Books data, with specific userId and bookId but when if that user don't have Books, still return the User data with empty books or null.

Mongo: 3.4

What I tried.

$this->mongo->selectCollection('users')->aggregate([
    ['$match' => ['userId' => $userId]],
    [
        '$lookup' => [
            'from' => 'books',
            'localField' => 'userId',
            'foreignField' => 'userId',
            'as' => 'books'
        ]
    ],
    ['$unwind' => [
        'path' => '$books',
    ]],
    ['$match' => ['books.bookId' => $bookId]]
]);

Problem: with above code, it will return empty result when book is not matched. I still want to get user data.

Sample result I expect are:

When book found

user : {
    userId: 1
    name: xxx,
    books: {
        name: Book name
        userId: 1
    }
}

When book not found

user : {
    userId: 1,
    name: xxx,
    books: null or whatever
}

Upvotes: 2

Views: 1029

Answers (1)

Ashh
Ashh

Reputation: 46441

You need to use $filter aggregation to filter the books array.

$this->mongo->selectCollection('users')->aggregate([
  [ '$match' => ['userId' => $userId]],
  [ '$lookup' => [
    'from' => 'books',
    'localField' => 'userId',
    'foreignField' => 'userId',
    'as' => 'books'
  ]],
  [ '$addFields' => [
    'books' => [
      '$filter' => [
        'input' => '$books',
        'as' => 'book',
        'cond' => [ '$eq' => ['$$book.userId', bookId]]
      ]
    ]
  ]]
])

Upvotes: 1

Related Questions