Mongo linking one array to another

Question

The starting point for this query is a collection called books:

{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'isbn': 8765434567890,
   'booktitle': 'some text',
   'chapters':
      [
         {
            'r_id': {"$oid": "64ba6a416504f1f01kkk333"},
            'pagenr': 7,
            'title': 'whatever',
         },
         {
            'r_id': {"$oid": "64ba6a416504f1f01dj3jd7f"},
            'pagenr': 12,
            'title': 'another title',
         },
      ]
}

There's of course much more to it. But this collection is the starting point for the query.

A second collection has notes about a lot of things in various collections, amongst others about the chapters in the above collection:

{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'r_id': {"$oid": "64ba6a416504f1f01kkk333"},
   'note': 'blabla',
},
{
   '_id' {"$oid": "64ba6a416504f1f01d888hhh"},
   'r_id': {"$oid": "64ba6a416504f1f01kkk333"},
   'note': 'more blabla',
},
{
   '_id' {"$oid": "55353536504f1f01d000000"},
   'r_id': {"$oid": "64ba6a416swldkjfdskjl34"},
   'note': 'blabla',
},

So more notes can be about the same chapter. Linked by the r_id oid in both collecions.

What I need is two almost similar things:

1 Get the complete record of one book by the isbn nr. And add the amount of notes per chapter to that record.

2 Get the complete record of one book by the isbn nr. And add all of the notes per chapter to that record.

NB The chapters are not ordered in the collection, but should be ordered by chapters.pagenr in the output.

So the output for 1 would be something like:

{
   '_id' {"$oid": "64ba6a416504f1f01d773faa"},
   'isbn': 8765434567890,
   'booktitle': 'some text',
   'chapters':
      [
         {
            'r_id': {"$oid": "64ba6a416504f1f01kkk333"},
            'pagenr': 7,
            'title': 'whatever',
            'notes': 2
         },
         {
            'r_id': {"$oid": "64ba6a416504f1f01dj3jd7f"},
            'pagenr': 12,
            'title': 'another title',
            'notes': 0
         },
      ]
}

For 2 notes would of course contain the array of notes. Almost the same, but not.

What I've come up with so far:

match = {'$match': {'isbn': {'$in': isbns}}}
project = {'$project':
        {
            '_id': '$_id',
            'isbn': '$isbn',
            'booktitle': '$booktitle',
            'chapters': {'$sortArray':
                {
                    'input': {'$map':
                                  {'input':
                                       '$chapters',
                                   'in': {
                                       'pagenr': '$$this.pagenr',
                                       'title': '$$this.title',
                                       'r_id': '$$this.r_id',
                                       }
                                   }
                              },
                    'sortBy': {'pagenr': 1},
                }
            },
            'notes': '$$related_notes',
            
      }
}
lookup = {'$lookup':
        {
            'from': 'notes',
            'localField': 'chapters.r_id',
            'foreignField': 'r_id',
            'as': 'related_notes',
        },
}

pipeline = [
        lookup,
        match,
        project,
    ]

This works, the notes are of course added to the end of the output. If I put them in the $map part, they are repeated numerous times and not filtered in any way.

By the way, coming from SQL, I really love Mongo. It's marvelous however not easy to grasp. Learning a lot about it.

Thanks for the help.

Answer 1

you can use $filter inside each map iteration where you filter the related_notes array to return an array which has notes having r_id of that particular iteration. Then you can get the size of the filtered array using $size.
Also you can do the $match before $lookup to reduce unnecessary lookups

db.books.aggregate([
  { $match: { isbn: { $in: isbns } } },
  { $lookup: { from: "notes", localField: "chapters.r_id", foreignField: "r_id", as: "related_notes" } },
  {
    $project: {
      _id: "$_id",
      isbn: "$isbn",
      booktitle: "$booktitle",
      chapters: {
        $sortArray: {
          input: {
            $map: {
              input: "$chapters",
              as: "chapter",
              in: {
                r_id: "$$chapter.r_id",
                pagenr: "$$chapter.pagenr",
                title: "$$chapter.title",
                notes: { $size: { $filter: { input: "$related_notes", as: "note", cond: { $eq: ["$$note.r_id", "$$chapter.r_id"] } } } }
              }
            }
          },
          sortBy: { pagenr: 1 }
        }
      }
    }
  }
])

playground