MongoDB Aggregation - Fetch data from ObjectId subarray-CodePudding

I have these collections:

author

[
  {
    _id: "63c242130b17d5516e0cb499",
    author_name:'Vyom',
    book_ids:["63c242330b17d5516e0cb49a","63c242410b17d5516e0cb49b"]
  }
]

book

[
  {
    _id:"63c242330b17d5516e0cb49a",
    author_id:'63c242130b17d5516e0cb499',
    book_name:'True Love',
    genere:'horror'
  },
  {
    _id:"63c242410b17d5516e0cb49b",
    author_id:'63c242130b17d5516e0cb499',
    book_name:'Monster Strike',
    genere:'romance'
  },
]

I want to fetch details of books in author collection aggregation if book_ids exists.

For this I tried as:

db.author.aggregate([
     {
       $match: {
         _id: ObjectId("63c242130b17d5516e0cb499")
       }
     },
     {
       $lookup:{
         from: 'book',
         localField: '_id',
         foreignField: 'author_id',
         as: 'book_details'
       }
     },
     {
        $addFields:{
           book_info: {
             $map: {
              input: '$book_details'
              as: 'el'
              in: {
                  $match: {_id:ObjectId('$$el._id')},
                  $paroject: {book_name: 1},
              }
             }
           }
        }                   
     }
])

But it throws:

Unrecognized error: '$match'.

Expected O/P:

[
  {
    _id: "63c242130b17d5516e0cb499",
    author_name:'Vyom',
    book_ids:["63c242330b17d5516e0cb49a","63c242410b17d5516e0cb49b"],
    book_info: [
      {
        _id:"63c242330b17d5516e0cb49a",
        book_name:'True Love',
      },
      {
        _id:"63c242410b17d5516e0cb49b",
        book_name:'Monster Strike',
      }
    ]
  }
]

Is there any other way to loop and get details? I tried looking for other solutions but was unable to find.

CodePudding user response：

I don't see why you need a $set stage to format the element in the book_details array for the book_info field.

You can use $lookup with pipeline to join both collections and format the array of documents returned.

db.author.aggregate([
  {
    $match: {
      _id: ObjectId("63c242130b17d5516e0cb499")
    }
  },
  {
    $lookup: {
      from: "book",
      localField: "book_ids",
      foreignField: "_id",
      as: "book_info",
      pipeline: [
        {
          $project: {
            _id: 1,
            book_name: 1
          }
        }
      ]
    }
  }
])

Demo @ Mongo Playground

CodePudding user response：

db.authors.aggregate([
    {
        $lookup: {
            from: "books",
            localField: "book_ids",
            foreignField: "_id",
            as: "books"
        }
    },
    {
        $match: { "books": { $ne: [] } }
    },
    {
        $project: {
            _id: 1,
            author_name: 1,
            books: {
                _id: 1,
                author_id: 1,
                book_name: 1,
                genre: 1
            }
        }
    }
])

This pipeline will give you the result where all the authors' book details will be there if they have any books