add subDocument for each document in array dynamically-CodePudding

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

For example: I have a collection for 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 an 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"}
}

CodePudding user response：

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

CodePudding user response：

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.