title: How to transform deeply nested data in mongodb aggregation framework?
body: <p>I’m a newbie with mongoDB aggregation and I’m struggling a bit trying to get my data to look the way I want. I’m a student completing a bootcamp
  And we are doing a project where we seed a database of our choice with from millions lines of CSV that was extracted from a SQL database, though Im not sure which one.
  For context, the data is questions and answers from a mock retail application we built.</p>
  <p>I was given three files. One with questions, one with answers, and one with photos that were uploaded to answers. I successfully used the $ lookup and $out operators
  To join these files on the appropriate index and export to a new collection . So now I just have a collection of questions, and a collections of ansPhotos</p>
  <p>The issue Is that the data needs to be structurally transformed for different cases.</p>
  <p>Suppose I want all the questions and answers for a particular product . Below shows how the question data is structured, giving me all questions for a product_id of 1:</p>
  <pre><code>   db.questions.find({ product_id: 1 })[
    ({
      _id: ObjectId('61731a1cae4ca5aef1836b04'),
      question_id: 4,
      product_id: 1,
      question_body: 'How long does it last?',
      question_date: Long('1594341317010'),
      asker_name: 'funnygirl',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 6,
    },
    {
      _id: ObjectId('61731a1cae4ca5aef1836b05'),
      question_id: 3,
      product_id: 1,
      question_body: 'Does this product run big or small?',
      question_date: Long('1608535907083'),
      asker_name: 'jbilas',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 8,
    },
    {
      _id: ObjectId('61731a1cae4ca5aef1836b06'),
      question_id: 6,
      product_id: 1,
      question_body: 'Is it noise cancelling?',
      question_date: Long('1608855284662'),
      asker_name: 'coolkid',
      asker_email: 'first.last@gmail.com',
      reported: 1,
      helpful: 19,
    },
    {
      _id: ObjectId('61731a1cae4ca5aef1836b08'),
      question_id: 1,
      product_id: 1,
      question_body: 'What fabric is the top made of?',
      question_date: Long('1595884714409'),
      asker_name: 'yankeelover',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 1,
    },
    {
      _id: ObjectId('61731a1cae4ca5aef1836b0d'),
      question_id: 5,
      product_id: 1,
      question_body: 'Can I wash it?',
      question_date: Long('1608855284662'),
      asker_name: 'cleopatra',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 7,
    },
    {
      _id: ObjectId('61731a1cae4ca5aef1836b13'),
      question_id: 2,
      product_id: 1,
      question_body: 'HEY THIS IS A WEIRD QUESTION!!!!?',
      question_date: Long('1613888219613'),
      asker_name: 'jbilas',
      asker_email: 'first.last@gmail.com',
      reported: 1,
      helpful: 4,
    })
  ];
  </code></pre>
  <p>I now want to get all the answers for all these questions. For brevity and because I’ll be pasting a lot of context / examples, heres what a couple of answer documents from ansPhotos looks like:</p>
  <pre><code>db.ansPhotos.find({question_id:4})
  
  
  [
    {
      _id: ObjectId(&quot;61731c9c39b2df95b4573b3c&quot;),
      id: 65,
      question_id: 4,
      body: 'It runs small',
      date: Long(&quot;1605784307205&quot;),
      answerer_name: 'dschulman',
      answerer_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 1,
      photos: [
        {
          _id: ObjectId(&quot;61731edbbac3ef59b2a59b04&quot;),
          id: 15,
          answer_id: 65,
          url: 'https://images.unsplash.com/photo-1536922645426-5d658ab49b81?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1650&amp;q=80'
        },
        {
          _id: ObjectId(&quot;61731edbbac3ef59b2a59b0a&quot;),
          id: 14,
          answer_id: 65,
          url: 'https://images.unsplash.com/photo-1470116892389-0de5d9770b2c?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1567&amp;q=80'
        }
      ]
    },
    {
      _id: ObjectId(&quot;61731c9c39b2df95b4573b54&quot;),
      id: 89,
      question_id: 4,
      body: 'Showing no wear after a few months!',
      date: Long(&quot;1599089609530&quot;),
      answerer_name: 'sillyguy',
      answerer_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 8,
      photos: []
    }
  ]
  </code></pre>
  <p>Now for the part I’m struggling with.</p>
  <p>The data needs to look different for different API calls. I basically need to nest every answer with its photos in each question.</p>
  <p>Here are the key challenges I’m facing and transformations I have to make. There are other transformations that I am not discussing because they are easy to do, such as not returning the Object_id for answers,
  Transforming the date etc.</p>
  <p>Each question has an answers object that is stored in key-value pairs with its “id” as the key and the object as the value.</p>
  <p>Each answer must only have the photos url in an array, instead of an array of objects that have a URL property each., as you can see above for answers related to question_id 4.</p>
  <p>Some questions do not have any answers. Question with “ question_id:3” below is one such question. I am still expected to return an empty object at the “answers” key if there are no questions for it.</p>
  <pre><code>   [
           {
               &quot;question_id&quot;: 4,
               &quot;question_body&quot;: &quot;How long does it last?&quot;,
               &quot;question_date&quot;: &quot;2020-07-10T00:35:17.010Z&quot;,
               &quot;asker_name&quot;: &quot;funnygirl&quot;,
               &quot;reported&quot;: false,
               &quot;question_helpfullness&quot;: 6,
               &quot;answers&quot;: {
                   &quot;65&quot;: {
               _id: ObjectId(&quot;61731c9c39b2df95b4573b3c&quot;),
                       &quot;id&quot;: 65,
                       &quot;question_id&quot;: 4,
                       &quot;body&quot;: &quot;It runs small&quot;,
                       &quot;date&quot;: 1605784307205,
                       &quot;answerer_name&quot;: &quot;dschulman&quot;,
                       &quot;answerer_email&quot;: &quot;first.last@gmail.com&quot;,
                       &quot;helpful&quot;: 1,
                       &quot;photos&quot;: [&quot;https://images.unsplash.com/photo-1536922645426-5d658ab49b81?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1650&amp;q=80”,
     &quot;https://images.unsplash.com/photo-1470116892389-0de5d9770b2c?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1567&amp;q=80&quot;]
                   },
                   &quot;89&quot;: {
                       &quot;_id&quot;: &quot;61731c9c39b2df95b4573b54&quot;,
                       &quot;id&quot;: 89,
                       &quot;question_id&quot;: 4,
                       &quot;body&quot;: &quot;Showing no wear after a few months!&quot;,
                       &quot;date&quot;: 1599089609530,
                       &quot;answerer_name&quot;: &quot;sillyguy&quot;,
                       &quot;answerer_email&quot;: &quot;first.last@gmail.com&quot;,
                       &quot;reported&quot;: 0,
                       &quot;helpful&quot;: 8,
                       &quot;photos&quot;: []
                   }
               }
           },
           {
               &quot;question_id&quot;: 5,
               &quot;question_body&quot;: &quot;Can I wash it?&quot;,
               &quot;question_date&quot;: &quot;2020-12-25T00:14:44.662Z&quot;,
               &quot;asker_name&quot;: &quot;cleopatra&quot;,
               &quot;reported&quot;: false,
               &quot;question_helpfullness&quot;: 7,
               &quot;answers&quot;: {
                   &quot;46&quot;: {
                       &quot;_id&quot;: &quot;61731c9c39b2df95b4573b27&quot;,
                       &quot;id&quot;: 46,
                       &quot;question_id&quot;: 5,
                       &quot;body&quot;: &quot;I've thrown it in the wash and it seems fine&quot;,
                       &quot;date&quot;: 1606022843272,
                       &quot;answerer_name&quot;: &quot;marcanthony&quot;,
                       &quot;answerer_email&quot;: &quot;first.last@gmail.com&quot;,
                       &quot;reported&quot;: 0,
                       &quot;photos&quot;: []
                   },
                   &quot;64&quot;: {
                       &quot;_id&quot;: &quot;61731c9c39b2df95b4573b3b&quot;,
                       &quot;id&quot;: 64,
                       &quot;question_id&quot;: 5,
                       &quot;body&quot;: &quot;It says not to&quot;,
                       &quot;date&quot;: 1588644950162,
                       &quot;answerer_name&quot;: &quot;ceasar&quot;,
                       &quot;answerer_email&quot;: &quot;first.last@gmail.com&quot;,
                       &quot;helpful&quot;: 0,
                       &quot;photos&quot;: []
                   },
                }
           },
           {
               &quot;question_id&quot;: 3,
               &quot;question_body&quot;: &quot;Does this product run big or small?&quot;,
               &quot;question_date&quot;: &quot;2020-12-21T07:31:47.083Z&quot;,
               &quot;asker_name&quot;: &quot;jbilas&quot;,
               &quot;reported&quot;: false,
               &quot;question_helpfullness&quot;: 8,
               &quot;answers&quot;: {}
               }
           },
  //etc..
  </code></pre>
  <p>What I’ve tried in the pipeline:</p>
  <p>Calling db.questions.aggregate([]) with the following stages.</p>
  <p>Get all products that have a product id of 1 and are not reported:
  Stage 1 :</p>
  <pre><code> {
      '$match': {
        'product_id': 1, 
        'reported': 0
      }
  }
  </code></pre>
  <p>Stage 2:</p>
  <p>Join all questions documents with their respective answers in an array called “answers”</p>
  <pre><code>  {
          '$lookup': {
            'from': 'ansPhotos', 
            'localField': 'question_id', 
            'foreignField': 'question_id', 
            'as': 'answers'
          }
  }
  </code></pre>
  <p>Sample output:</p>
  <pre><code>questions_answers&gt; db.questions.aggregate([{$match:{product_id:1,reported:0}},{$lookup:{from:'ansPhotos',localField:'question_id',foreignField:'question_id',as:'answers'}}])
  [
    {
      _id: ObjectId(&quot;61731a1cae4ca5aef1836b04&quot;),
      question_id: 4,
      product_id: 1,
      question_body: 'How long does it last?',
      question_date: Long(&quot;1594341317010&quot;),
      asker_name: 'funnygirl',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 6,
      answers: [
        {
          _id: ObjectId(&quot;61731c9c39b2df95b4573b3c&quot;),
          id: 65,
          question_id: 4,
          body: 'It runs small',
          date: Long(&quot;1605784307205&quot;),
          answerer_name: 'dschulman',
          answerer_email: 'first.last@gmail.com',
          reported: 0,
          helpful: 1,
          photos: [
            {
              _id: ObjectId(&quot;61731edbbac3ef59b2a59b04&quot;),
              id: 15,
              answer_id: 65,
              url: 'https://images.unsplash.com/photo-1536922645426-5d658ab49b81?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1650&amp;q=80'
            },
            {
              _id: ObjectId(&quot;61731edbbac3ef59b2a59b0a&quot;),
              id: 14,
              answer_id: 65,
              url: 'https://images.unsplash.com/photo-1470116892389-0de5d9770b2c?ixlib=rb-1.2.1&amp;ixid=eyJhcHBfaWQiOjEyMDd9&amp;auto=format&amp;fit=crop&amp;w=1567&amp;q=80'
            }
          ]
        },
        {
          _id: ObjectId(&quot;61731c9c39b2df95b4573b54&quot;),
          id: 89,
          question_id: 4,
          body: 'Showing no wear after a few months!',
          date: Long(&quot;1599089609530&quot;),
          answerer_name: 'sillyguy',
          answerer_email: 'first.last@gmail.com',
          reported: 0,
          helpful: 8,
          photos: []
        }
      ]
    },
    {
      _id: ObjectId(&quot;61731a1cae4ca5aef1836b05&quot;),
      question_id: 3,
      product_id: 1,
      question_body: 'Does this product run big or small?',
      question_date: Long(&quot;1608535907083&quot;),
      asker_name: 'jbilas',
      asker_email: 'first.last@gmail.com',
      reported: 0,
      helpful: 8,
      answers: []
    },
  
  //etc… 
  ] 
  </code></pre>
  <p>Stage 3:
  unwind each answer array , preserving null arrays because I still need to return questions without answers.</p>
  <pre><code>{
      '$unwind': {
        'path': '$answers', 
        'preserveNullAndEmptyArrays': true
      }
    }
  </code></pre>
  <p>I then have a document for each answer and can manipulate the “answers.photos” object. Each answers field is now an object containing the answers.</p>
  <p>STAGE 4</p>
  <p>Things become muddy here.
  For example, I’ve tried to use <code>$addFields</code>, <code>$set</code> and <code>$project</code> to just get the photos.url property for each answer and put it in an array. I’ve had some success doing this. but…</p>
  <p>STAGE 5</p>
  <p>Then I try to $group them back into arrays of objects And had some success with it… Note the <code>$ifNull</code> is my feeble attempt to give the next stage what it wants, but it is not working.</p>
  <pre><code>{
  $group: {“answers.id”}, 
    question_id:{$first:&quot;$question_id&quot;}, question_body:{$first:'$question_body'},question_date:{$first:&quot;$question_date&quot;},asker_name:{$first:&quot;$asker_name&quot;},reported:{$first:&quot;$reported&quot;},question_helpfullness:{$first:&quot;$helpful&quot;},
  answers:{$push:{$ifNull:[&quot;$answers&quot;,{_id:&quot;$_id&quot;,id:'noanswers' ,question_id:&quot;$question_id&quot;,}]}}
  
  } 
  </code></pre>
  <p>BUT I also need to do this at some point:</p>
  <p>STAGE 6 or later…</p>
  <pre><code> {
      '$addFields': {
        'answers': {
          '$arrayToObject': {
            '$map': {
              'input': '$answers', 
              'in': {
                'k': {
                  '$toString': '$$this.id'
                }, 
                'v': '$$this'
              }
            }
          }
        }
      }
    }
  </code></pre>
  <p>To give me the appropriate key-value pairs as seen in the desired output.</p>
  <p>This is where things get muddy. I have tried a TON of configurations over the last 5 days.</p>
  <p>In most cases if I directly manipulate the answers array after stage 4, I get this error when I then try to use $addFields:</p>
  <pre><code>PlanExecutor error during aggregation :: caused by :: $arrayToObject requires an object with keys ‘k’ and ‘v’, where the value of ‘k’ must be of type string. Found type: null
  </code></pre>
  <p>This is because the question with id 3 has no answers and I’ve inadvertently assigned it to an empty object using any of the methods mentioned in Stage 4.
  I’ve tried some $ifNull operations as you can see to give the this question the key value pairs its expecting, but am only successful sometimes and usually there are other weird side effects.</p>
  <p>To summarize, is there a way for me to get only the url property out of the “answers.photos” array, account for the edge case of having a question that has no answers, and still structure them in the key value pairs as illustrated?</p>
  <p>Apologies if this is too long or difficult to read. If there’s some more formatting I can do to make it better please let me know. Any help is very very much appreciated.</p>
  <p>Joe</p>