Mongodb Array and object got mixed up inside array

Question

I have the below document in mongodb, where grades key is an array with the combination of objects and array. I have several records like this in my db. i wanted to update the array and have it as an object for all such records. How can i achieve this with update query ?

 {
        _id: 4,
        grades: [
           { grade: 80, mean: 75, std: 8 },
           { grade: 85, mean: 90, std: 5 },
           [{ grade: 85, mean: 85, std: 8 }]
        ]
      }

Expected output :

 {
        _id: 4,
        grades: [
           { grade: 80, mean: 75, std: 8 },
           { grade: 85, mean: 90, std: 5 },
           { grade: 85, mean: 85, std: 8 }
        ]
      }

Not very clear on your objective. Do you mean to say, you want to convert the last item in the "grades" array - change it from an array itself into an object? — barrypicker
– barrypicker, Commented Sep 23, 2021 at 16:38
Yes. want to convert the array type present in "grades" to object . — Jagadeesh
– Jagadeesh, Commented Sep 23, 2021 at 16:43
Do you want to fetch the data in a specific shape (i.e., convert the nested array into a document on the fly) or do you need to actually change the shape of the data at rest (in the database)? — barrypicker
– barrypicker, Commented Sep 23, 2021 at 16:45
Need to change the shape of the document in DB. Mongo version 3.4 — Jagadeesh
– Jagadeesh, Commented Sep 23, 2021 at 16:46
Do you need to change all the documents at once, or can you use the Schema Versioning pattern (change them as you encounter them in your app) ? - mongodb.com/blog/post/… — barrypicker
– barrypicker, Commented Sep 23, 2021 at 16:49

turivishal · Accepted Answer · 2021-09-23 17:58:56Z

1

It is not possible to do this using single update query in MongoDB 3.4,

1) You can do 2 queries first find and second update in loop, You can execute in mongo shell or any MongoDB editor:

find() query to project require field and loop the result
for loop the grades array
check if condition the grades element is array then concat otherwise return same
update() query to update new grades array

db.getCollection('grades').find({}, { grades: 1 }).forEach(function (doc) {
    var grades = [];
    for(var i = 0; i < doc.grades.length; i++) {
        if (Array.isArray(doc.grades[i])) {
            grades = grades.concat(doc.grades[i]);
        }
        else {
            grades.push(doc.grades[i]);
        }
    }
    // update
    db.getCollection('grades').update(
        { _id: doc._id }, 
        { $set: { grades: grades } }
    )
})

2) you can try aggregation query and export updated results in the new collection using single query,

$reduce to iterate loop of grades array
$type to get data type of the element
$concatArrays to concat multiple arrays
$cond check condition if element type is array then concat directly both arrays otherwise concat with array into array
$out to export collection in new collection

db.collection.aggregate([
  {
    $addFields: {
      grades: {
        $reduce: {
          input: "$grades",
          initialValue: [],
          in: {
            $cond: [
              { $eq: [{ $type: "$$this" }, "array"] },
              { $concatArrays: ["$$value", "$$this"] },
              { $concatArrays: ["$$value", ["$$this"]] }
            ]
          }
        }
      }
    }
  },
  { $out: "collection name" }
])

Playground

3) Alternate option using update with aggregation pipeline query above MongoDB 4.2,

db.collection.updateMany({},
  [{
    $set: {
      grades: {
        $reduce: {
          input: "$grades",
          initialValue: [],
          in: {
            $cond: [
              { $eq: [{ $type: "$$this" }, "array"] },
              { $concatArrays: ["$$value", "$$this"] },
              { $concatArrays: ["$$value", ["$$this"]] }
            ]
          }
        }
      }
    }
  }]
)

Playground

edited Sep 23, 2021 at 17:58

answered Sep 23, 2021 at 16:51

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

3 Comments

barrypicker Over a year ago

This makes the assumption the nested array only has a single document entry. Not sure this is true.

Jagadeesh Over a year ago

Ok atleast can we do update with some specific mentioned records with id of it ?

turivishal Over a year ago

@Jagadeesh updated the first approach for 3.4. the second approach will work on 4.2. I yes you can give the id in query part of the find method.

Collectives™ on Stack Overflow

Mongodb Array and object got mixed up inside array

1 Answer 1

1) You can do 2 queries first find and second update in loop, You can execute in mongo shell or any MongoDB editor:

2) you can try aggregation query and export updated results in the new collection using single query,

3) Alternate option using update with aggregation pipeline query above MongoDB 4.2,

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1) You can do 2 queries first find and second update in loop, You can execute in mongo shell or any MongoDB editor:

2) you can try aggregation query and export updated results in the new collection using single query,

3) Alternate option using update with aggregation pipeline query above MongoDB 4.2,

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related