MongoDB aggregation from nested array of objects

Question

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.

you don't need mongoose, you can use your programming language driver, google nodejs mongodb driver to find it if you use javascript — Takis
– Takis, Commented Dec 15, 2022 at 19:24

Takis · Accepted Answer · 2022-12-15 22:41:14Z

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"]}])

Thank you so much! There is one more thing though. This aggregation seems to remove visibility key from the object with contentId. How can I modify this to keep the key?
yes you are right i forgot it, i added one more $mergeObjects to not replace it with the matched, but to merge them to keep all fields, i think now its ok

Collectives™ on Stack Overflow

MongoDB aggregation from nested array of objects

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related