CODEWITHSUNDEEP
arraysmongodbfieldmongo-shell
amota
amota

Reputation: 331

MongoDB: How to take multiple fields within a document and output their values into an array (as a new field)?

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!

Upvotes: 0

Views: 1264

Answers (2)

amota
amota

Reputation: 331

Thank you to @Wernfried for the original solution to this question. I have modified the solution to incorporate my "Note" about ignoring any empty monthlyProd# values (aka months that didn't have any production), so that they are not added into the final monthlyProd array.

To do this, I added an $and operator to the cond: within $filter, and added the following as the second expression for the $and operator (I used "" and {} to take care of the empty field values if they are of either string or object data type):

{ $not: { $in: [ "$$this.v", [ "", {} ] ] } }

Final solution:

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

Thanks again @Wernfried and Stackoverflow community!

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

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

Upvotes: 1

Related Questions