MongoDB aggregation from nested array of objects-CodePudding

I'm working on a school project and I need to join the subject document with content documents on contentId. However, I can't use mongoose because I'm working with a stack provided by the school. Here's an example to explain my problem:

Example subject document:

{
  _id: ObjectId('000000000000000000000100')
  name: "My subject"
  description: "Lorem ipsum..."
  language: "en",
  topics: [
    {
      id: ObjectId('000000000000000000000200'),
      name: "The first topic",
      description: "Lorem ipsum...",
      content: [
        {
          contentId: ObjectId('000000000000000000000300'),
          visibility: true,
        },
        {
          url: "stackoverflow.com",
          title: "Stack Overflow",
          type: "website"
        }
      ]
    }
  ]
}

Example content document:

{
  _id: ObjectId('000000000000000000000300'),
  title: "Example content",
  description: "Example course in MongoDB",
  url: "http://example.com/believe/apparatus.php?birthday=bed&boot=bead"
  type: "other"
}

I need the result of the aggregation to look like this:

{
  _id: ObjectId('000000000000000000000100')
  name: "My subject"
  description: "Lorem ipsum..."
  language: "en",
  topics: [
    {
      id: ObjectId('000000000000000000000200'),
      name: "The first topic",
      description: "Lorem ipsum...",
      content: [
        {
          _id: ObjectId('000000000000000000000300'),
          title: "Example content",
          description: "Example course in MongoDB",
          url: "http://example.com/believe/apparatus.php?birthday=bed&boot=bead"
          type: "other"
          visibility: true,
        },
        {
          url: "stackoverflow.com",
          title: "Stack Overflow",
          type: "website"
        }
      ]
    }
  ]
}

I have tried some $unwind and $lookup combinations but none of them returned the correct result.

Is there a way to do this without using mongoose? Thank you.

CodePudding user response：

Query

lookup-fields will be the topics.content.contentId an array with all the values we want to join
do the lookup, matching documents will go on results
and then go inside with nested $map to put the values that we found that they match. (the $filter is used to get from results the document that matched so we replaced it)

*this is a way to avoid the double unwind and keep the document structure as it is, alternative ways exists, to make the last $set simpler but i don't think they are faster (like single unwind and lookup and then group) with this we don't unwind at all.

Playmongo

aggregate(
[{"$set": 
   {"lookup-fields": 
     {"$reduce": 
       {"input": "$topics.content.contentId",
        "initialValue": [],
        "in": 
         {"$let": 
           {"vars": {"this": "$$value", "value": "$$this"},
            "in": {"$concatArrays": ["$$value", "$$this"]}}}}}}},
 {"$lookup": 
   {"from": "content",
    "localField": "lookup-fields",
    "foreignField": "_id",
    "as": "results"}},
 {"$set": 
   {"topics": 
     {"$map": 
       {"input": "$topics",
        "as": "lv1",
        "in": 
         {"$mergeObjects": 
           ["$$lv1",
             {"content": 
               {"$map": 
                 {"input": "$$lv1.content",
                  "as": "lv2",
                  "in": 
                   {"$cond": 
                     [{"$in": ["$$lv2.contentId", "$lookup-fields"]},
                       {"$mergeObjects": 
                         [{"$first": 
                             {"$filter": 
                               {"input": "$results",
                                "cond": {"$eq": ["$$this._id", "$$lv2.contentId"]}}}},
                          "$$lv2"]},
                      "$$lv2"]}}}}]}}}}},
 {"$unset": ["lookup-fields", "results"]}])