Mongodb unwind array nested in array

Question

This is my collection:

db.questions.insertMany([
  {
    "content": [
      {
        "languageId": "en",
        "text": "What are you planning to buy today at the supermarket?"
      },
      {
        "languageId": "nl",
        "text": "Wat ben je van plan om vandaag in de supermarkt te kopen?"
      },
    ],
    "type": "multipleChoice",
    "multipleChoice": {
      "numAnswers": { "min": 1, "max": 1 },
      "possibleAnswers": [
        {
          "sequence": 1,
          "content": [
            {
              "languageId": "en",
              "text": "apples"
            },
            {
              "languageId": "nl",
              "text": "appels"
            },
          ],
        },
        {
          "sequence": 2,
          "content": [
            {
              "languageId": "en",
              "text": "peers"
            },
            {
              "languageId": "nl",
              "text": "peren"
            },
          ],
        },
      ],
    }
  },
  {
    "content": [
      {
        "languageId": "en",
        "text": "How do you feel?"
      },
      {
        "languageId": "nl",
        "text": "Hoe voel je je?"
      },
    ],
    "type": "ranking1to5",
  }
]);

I want to transform into one language that are in two content arrays. So I want to have the output:

{
    "_id" : ObjectId("5abe4c09d3831890de28ec8f"),
    "content" : 
        {
            "languageId" : "en",
            "text" : "What are you planning to buy today at the supermarket?"
        }, 
    "type" : "multipleChoice",
    "multipleChoice" : {
        "numAnswers" : {
            "min" : 1.0,
            "max" : 1.0
        },
        "possibleAnswers" : [ 
            {
                "sequence" : 1.0,
                "content" : 
                    {
                        "languageId" : "en",
                        "text" : "apples"
                    }
            }, 
            {
                "sequence" : 2.0,
                "content" :  
                    {
                        "languageId" : "en",
                        "text" : "peers"
                    }
            }
        ]
    }
}

/* 2 */
{
    "_id" : ObjectId("5abe4c09d3831890de28ec90"),
    "content" :  
        {
            "languageId" : "en",
            "text" : "How do you feel?"
        },
    "type" : "ranking1to5"
}

I tried using $unwind, $match and $group to tackle this problem. I have come pretty far only the last piece does not work:

db.getCollection('questions').aggregate([
    { $unwind: "$content" },
    { $match: { "content.languageId": "en" } },
    { $unwind: { path: '$multipleChoice.possibleAnswers', preserveNullAndEmptyArrays: true } },
    { $unwind: { path: '$multipleChoice.possibleAnswers.content', preserveNullAndEmptyArrays: true } },
    { $match: { "multipleChoice.possibleAnswers.content.languageId": "en" } },
    { $group: { _id: "$_id", content: { $first: "$content" }, type: { $first: "$type" }, multipleChoice: { $addToSet: "$multipleChoice" } } }
])

The problem is multipleChoice gets repeated while this should be possibleAnswers. Also the question that has no multipleChoice object should be included.

Any help is particularly appreciated !!

cbartosiak · Accepted Answer · 2018-03-30 23:57:21Z

1

It looks like a good fit for the $redact. Please try the following aggregation:

db.questions.aggregate(
    [
        {
            $redact: {
                $cond: {
                    if: {
                        $eq: [
                            { $ifNull: [ "$languageId", "en" ] },
                            "en"
                        ]
                    },
                    then: "$$DESCEND",
                    else: "$$PRUNE"
                }
            }
        },
        {
            $addFields: {
                "content": { $arrayElemAt: [ "$content", 0 ] },
                "multipleChoice.possibleAnswers": {
                    $map: {
                        input: "$multipleChoice.possibleAnswers",
                        as: "possibleAnswer",
                        in: {
                            "sequence": "$$possibleAnswer.sequence",
                            "content": { $arrayElemAt: [ "$$possibleAnswer.content", 0 ] }
                        }
                    }
                }
            }
        },
        {
            $redact: {
                $cond: {
                    if: { $eq: [ "$possibleAnswers", null ] },
                    then: "$$PRUNE",
                    else: "$$DESCEND"
                }
            }
        }
    ]
);

answered Mar 30, 2018 at 23:57

cbartosiak

7856 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Herman Fransen Over a year ago

It gives: InternalError: too much recursion

cbartosiak Over a year ago

It works for the example you gave. Please tell me more about your schema, what other fields documents can have, how many nested documents etc. Could you also tell me which stage is failing?

Herman Fransen Over a year ago

Sorry, I tried it on the example I gave you in Robomongo 1.0, and this was given the error InternalError: too much recursion. But now I tried it another way and it works perfectly!! I thank you very much for your time and effort !!

Herman Fransen Over a year ago

Do you know some good resources to learn more about the $redact function?

cbartosiak Over a year ago

The $ifNull is true when a field is null or not existing. We need this operator to "set" the languageId for all document levels which do not have it. Without it $$PRUNE would be called at the root level of both the input documents (at this level there is no languageId), so we would get no results. In fact we "cheat" the $redact doing so.

|

Collectives™ on Stack Overflow

Mongodb unwind array nested in array

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related