Add subdocument for each document in array dynamically

user_11889994 0
Viewed 80

I want to $addField for every document in a sub-array with the name as arrayName_index (don't know how long the array is).

For example: I have a collection of fun activities that looks like:

{
  _id: "1", 
  activity: movie,
  attendees: [
    {firsName: "personA", lastName: "lasty"},
    {firsName: "personB", lastName: "namey"},
    {firsName: "personC", lastName: "blabla"}
  ]},
{
  _id: "2", 
  activity: bowling,
  attendees: [{firsName: "personA", lastName: "lasty"}]
}

I want a MongoDB aggregation to create output as:

{
  _id: "1", 
  activity: movie,
  attendees: [
    {firsName: "personA", lastName: "lasty"},
    {firsName: "personB", lastName: "namey"},
    {firsName: "personC", lastName: "blabla"}
  ],
  attendees_1: {firsName: "personA", lastName: "lasty"},
  attendees_2: {firsName: "personB", lastName: "namey"},
  attendees_3: {firsName: "personC", lastName: "blabla"},
},
{
  _id: "2", 
  activity: bowling,
  attendees: [{firsName: "personA", lastName: "lasty"}],
  attendees_1: {firsName: "personA", lastName: "lasty"}
}
nosql-aggregation mongoose aggregation-framework mongodb-query mongodb
2 Answers

A bit long and complex query.

Concept

1.0 $replaceRoot - Replace the input document to specific document

1.1 $mergeObjects - Merge current document ($$ROOT) with the object generated in 1.2.

1.2 $arrayToObject - Convert array to single document from 1.3.

1.3 $reduce - Iterate each element from the array and transform it to a single value.

1.4 $concatArrays - Merge array.

1.4.1 Create document with

1.4.1.1 k (key) with prefix "attendees_" and get the index of current iterate document ($indexOfArray) + 1.

1.4.1.2 v (value) with current iterate document.

db.collection.aggregate([
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            "$arrayToObject": {
              "$reduce": {
                "input": "$attendees",
                "initialValue": [],
                "in": {
                  "$concatArrays": [
                    "$$value",
                    [
                      {
                        k: {
                          "$concat": [
                            "attendees_",
                            {
                              "$toString": {
                                "$add": [
                                  {
                                    "$indexOfArray": [
                                      "$attendees",
                                      "$$this"
                                    ]
                                  },
                                  1
                                ]
                              }
                            }
                          ]
                        },
                        v: "$$this"
                      }
                    ]
                  ]
                }
              }
            }
          }
        ]
      }
    }
  }
])

Sample Mongo Playground

user_80176900

Maybe slightly better since this avoids array searches, but it's largely the same as Yong Shun's answer.

db.collection.aggregate([
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            "$arrayToObject": {
              "$reduce": {
                "input": { "$range": [ 0, { "$size": "$attendees" } ] },
                "initialValue": [],
                "in": {
                  "$concatArrays": [
                    "$$value",
                    [
                      {
                        k: {
                          "$concat": [
                            "attendees_",
                            { "$toString": { "$add": [ 1, "$$this" ] } }
                          ]
                        },
                        v: { "$arrayElemAt": [ "$attendees", "$$this" ] }
                      }
                    ]
                  ]
                }
              }
            }
          }
        ]
      }
    }
  }
])

Try it on mongoplayground.net.

user_14093740
Related