MongoDB: How to take multiple fields within a document and output their values into an array (as a n-CodePudding

MongoDB: 4.4.9, Mongosh: 1.0.4

I have a MongoDB collection full of documents with monthly production data as separate fields (monthlyProd1, monthlyProd2, etc.). Each field is one month's production data, and the values are an object data type.

Document example:

_id: ObjectId("314e0e088f183fb7e699d635")
name: "documentName"
monthlyProd1: Object
monthlyProd2: Object
monthlyProd3: Object
...

I want to take all the months and put them into a single new field (monthlyProd) -- a single array of objects.

I can't seem to access the fields with the different methods I've tried. For example, this gets close to doing what I want:

db.monthlyProdData.updateMany({}, 
    { $push: { "monthlyProd": { $each: [ "$monthlyProd1", "$monthlyProd2", "$monthlyProd3" ] } } }
)

...but instead of taking the value / object data from each field, like I had hoped, it just outputs a string into the monthlyProd array ("$monthlyProd1", "$monthlyProd2", ...):

Actual output:

monthlyProd: Array
    0:  "$monthlyProd1"
    1:  "$monthlyProd2"
    2:  "$monthlyProd3"
    ...

Desired output:

monthlyProd: Array
    0:  Object
    1:  Object
    2:  Object
    ...

I want the data, not a string! Lol. Thank you for your help!

Note: some months/fields may be an empty string ("") because there was no production. I want to make sure to not add empty strings into the array -- only months with production / fields that have an object data type. That being said, I can try figuring that out on my own, if I can just get access to these fields' data!

CodePudding user response：

Try this one:

db.collection.updateMany({}, [
  // convert to k-v Array
  { $set: { monthlyProd: { $objectToArray: "$$ROOT" } } },
  {
    $set: {
      monthlyProd: {
        // removed not needed objects
        $filter: {
          input: "$monthlyProd",
          cond: { $not: { $in: [ "$$this.k", [ "name", "_id" ] ] } }
          // or cond: { $in: [ "$$this.k", [ "monthlyProd1", "monthlyProd2", "monthlyProd3" ] ] }
        }
      }
    }
  },
  // output array value
  { $project: { monthlyProd: "$monthlyProd.v" } }
])

Mongo playground