Mongo嵌套数组操作

mongo更新数组元素：
https://docs.mongodb.com/manual/reference/operator/update/positional-filtered/

使用类似这种形式的$[<identifier>]运算符

通式如下：
{ <update operator>: { "<array>.$[<identifier>]" : value } },
{ arrayFilters: [ { <identifier>: <condition> } ] }

<array>是数组的KEY
<identifier>代表某个元素，可以是自定义变量名element,elem,degree,或0，1，2...等数组下标
arrayFilters是关键字

实例：
> db.students.insertMany([{ "_id" : 1, "grades" : [ 95, 92, 90 ] },{ "_id" : 2,"grades" : [ 98, 100, 102 ] }, { "_id" : 3, "grades" : [ 95, 110, 100 ] }])
{ "acknowledged" : true, "insertedIds" : [ 1, 2, 3 ] }

> db.students.find()
{ "_id" : 1, "grades" : [ 95, 92, 90 ] }
{ "_id" : 2, "grades" : [ 98, 100, 102 ] }
{ "_id" : 3, "grades" : [ 95, 110, 100 ] }

To update all elements that are greater than or equal to 100 in the grades array, use the filtered positional operator $[<identifier>] with the arrayFilters:

将数组中大于等于100的元素值全部置成100

db.students.update(
   { },
   { $set: { "grades.$[element]" : 100 } },
   { multi: true,
     arrayFilters: [ { "element": { $gte: 100 } } ]
   }
)

如果数据格式不一致，比如加一条：
> db.students.insert({ "_id" : 5, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 8 }, { "grade" : 85, "mean" : 90, "std" : "yyy", "stu" : "xxx" }, { "grade"
: 85, "mean" : 85, "std":8}]})

再执行此命令会报错：
> db.students.update(    { },    { $set: { "grades.$[element]" : 100 } },    { multi: true,      arrayFilters: [ { "element": { $gte: 90 } } ]    } )
WriteResult({
        "nMatched" : 0,
        "nUpserted" : 0,
        "nModified" : 0,
        "writeError" : {
                "code" : 2,
                "errmsg" : "The path 'grades' must exist in the document in orde
r to apply array updates."
        }
})

这时需要加查询条件：
> db.sites4.update(    {"_id":{$lt:4} },    { $set: { "grades.$[element]" : 100
} },    { multi: true,      arrayFilters: [ { "element": { $gte: 90 } } ]    } )

> db.students.find()
{ "_id" : 1, "grades" : [ 85, 80, 80 ] }
{ "_id" : 2, "grades" : [ 88, 100, 100 ] }
{ "_id" : 3, "grades" : [ 85, 100, 100 ] }
{ "_id" : 5, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 8 }, { "grade" :
85, "mean" : 90, "std" : "yyy", "stu" : "xxx" }, { "grade" : 85, "mean" : 85, "s
td" : 8 } ] }

如果数组中的数据都一样：
> db.students.update(    { },    { $set: { "grades.$[element]" : 100 } },    { m
ulti: true,      arrayFilters: [ { "element": { $gte: 90 } } ]    } )
WriteResult({ "nMatched" : 3, "nUpserted" : 0, "nModified" : 3 })

> db.students.find()
{ "_id" : 1, "grades" : [ 100, 100, 100 ] }
{ "_id" : 2, "grades" : [ 100, 100, 100 ] }
{ "_id" : 3, "grades" : [ 100, 100, 100 ] }

可以改其中一行
> db.students.update(    {"_id":1 },    { $set: { "grades.$[element]" : 90 } },
   { multi: true,      arrayFilters: [ { "element": { $gte: 90 } } ]    } )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.students.find()
{ "_id" : 1, "grades" : [ 90, 90, 90 ] }
{ "_id" : 2, "grades" : [ 100, 100, 100 ] }
{ "_id" : 3, "grades" : [ 100, 100, 100 ] }

如果一行中的数值都一样可以使用下标

> db.students.update(    {"_id":1 },    { $set: { "grades.1" : 92 } },    { mult
i: true  } )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

下标从0开始，本例将第二个90改为92

> db.students.find()
{ "_id" : 1, "grades" : [ 90, 92, 90 ] }
{ "_id" : 2, "grades" : [ 100, 100, 100 ] }
{ "_id" : 3, "grades" : [ 100, 100, 100 ] }

如果嵌套形式如下：
> db.students2.insertMany([ {    "_id" : 1,    "grades" : [       { "grade" : 80
, "mean" : 75, "std" : 6 },       { "grade" : 85, "mean" : 90, "std" : 4 },
  { "grade" : 85, "mean" : 85, "std" : 6 }    ] }, {    "_id" : 2,    "grades" :
 [       { "grade" : 90, "mean" : 75, "std" : 6 },       { "grade" : 87, "mean"
: 90, "std" : 3 },       { "grade" : 85, "mean" : 85, "std" : 4 }    ] }])
{ "acknowledged" : true, "insertedIds" : [ 1, 2 ] }

> db.students2.find()
{ "_id" : 1, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 6 }, { "grade" :
85, "mean" : 90, "std" : 4 }, { "grade" : 85, "mean" : 85, "std" : 6 } ] }
{ "_id" : 2, "grades" : [ { "grade" : 90, "mean" : 75, "std" : 6 }, { "grade" :
87, "mean" : 90, "std" : 3 }, { "grade" : 85, "mean" : 85, "std" : 4 } ] }

修改grades.grade大于等于85的记录，让grades.mean等于100，这里的关键字是elem
> db.students2.update(
...    { },
...    { $set: { "grades.$[elem].mean" : 100 } },
...    {
...      multi: true,
...      arrayFilters: [ { "elem.grade": { $gte: 85 } } ]
...    }
... )
WriteResult({ "nMatched" : 2, "nUpserted" : 0, "nModified" : 2 })

> db.students2.find()
{ "_id" : 1, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 6 }, { "grade" :
85, "mean" : 100, "std" : 4 }, { "grade" : 85, "mean" : 100, "std" : 6 } ] }
{ "_id" : 2, "grades" : [ { "grade" : 90, "mean" : 100, "std" : 6 }, { "grade" :
 87, "mean" : 100, "std" : 3 }, { "grade" : 85, "mean" : 100, "std" : 4 } ] }
>

运行后如下效果
> db.students2.update(
...    { },
...    { $inc: { "grades.$[elem].std" : -1 } },
...    { arrayFilters: [ { "elem.grade": { $gte: 80 }, "elem.std": { $gt: 5 } }
], multi: true }
... )
WriteResult({ "nMatched" : 2, "nUpserted" : 0, "nModified" : 2 })

> db.students2.find()
{ "_id" : 1, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 5 }, { "grade" :
85, "mean" : 100, "std" : 4 }, { "grade" : 85, "mean" : 100, "std" : 5 } ] }
{ "_id" : 2, "grades" : [ { "grade" : 90, "mean" : 100, "std" : 5 }, { "grade" :
 87, "mean" : 100, "std" : 3 }, { "grade" : 85, "mean" : 100, "std" : 4 } ] }

-1，+5不是位置，是表达式+=，正数可以没有+号
> db.students2.update(    { },    { $inc: { "grades.$[elem].std" : +5 } },    {
arrayFilters: [ { "elem.grade": { $gte: 80 }, "elem.std": { $gt: 4 } } ], multi:
 true } )
WriteResult({ "nMatched" : 2, "nUpserted" : 0, "nModified" : 2 })
> db.students2.find()
{ "_id" : 1, "grades" : [ { "grade" : 80, "mean" : 75, "std" : 10 }, { "grade" :
 85, "mean" : 100, "std" : 4 }, { "grade" : 85, "mean" : 100, "std" : 10 } ] }
{ "_id" : 2, "grades" : [ { "grade" : 90, "mean" : 100, "std" : 10 }, { "grade"
: 87, "mean" : 100, "std" : 3 }, { "grade" : 85, "mean" : 100, "std" : 4 } ] }

更复杂的嵌套

> db.alumni.insertMany([{
...    "_id": 1,
...    "name": "Christine Franklin",
...    "degrees": [
...       { "level": "Master",
...         "major": "Biology",
...         "completion_year": 2010,
...         "faculty": "Science"
...       },
...       {
...         "level": "Bachelor",
...         "major": "Biology",
...         "completion_year": 2008,
...         "faculty": "Science"
...       }
...    ],
...    "school_email": "cfranklin@example.edu",
...    "email": "christine@example.com"
... },
... {
...    "_id": 2,
...    "name": "Reyansh Sengupta",
...    "degrees": [
...       { "level": "Bachelor",
...         "major": "Chemical Engineering",
...         "completion_year": 2002,
...         "faculty": "Engineering"
...       }
...    ],
...    "school_email": "rsengupta2@example.edu"
... }])
{ "acknowledged" : true, "insertedIds" : [ 1, 2 ] }

> db.alumni.find()
{ "_id" : 1, "name" : "Christine Franklin", "degrees" : [ { "level" : "Master",
"major" : "Biology", "completion_year" : 2010, "faculty" : "Science" }, { "level
" : "Bachelor", "major" : "Biology", "completion_year" : 2008, "faculty" : "Scie
nce" } ], "school_email" : "cfranklin@example.edu", "email" : "christine@example
.com" }
{ "_id" : 2, "name" : "Reyansh Sengupta", "degrees" : [ { "level" : "Bachelor",
"major" : "Chemical Engineering", "completion_year" : 2002, "faculty" : "Enginee
ring" } ], "school_email" : "rsengupta2@example.edu" }
>

> db.alumni.update(
...    { },
...    { $set : { "degrees.$[degree].gradcampaign" : 1 } },
...    { arrayFilters : [ {"degree.level" : { $ne: "Bachelor" } } ],
...      multi : true }
... )
WriteResult({ "nMatched" : 2, "nUpserted" : 0, "nModified" : 1 })

$ne(not exist)
修改后的状态，degree是变量名可自定义，比如x.level，x代表某个数组元素
> db.alumni.find()

{ "_id" : 1, "name" : "Christine Franklin", "degrees" : [ { "level" : "Master",
"major" : "Biology", "completion_year" : 2010, "faculty" : "Science", "gradcampa
ign" : 1 }, { "level" : "Bachelor", "major" : "Biology", "completion_year" : 200
8, "faculty" : "Science" } ], "school_email" : "cfranklin@example.edu", "email"
: "christine@example.com" }
{ "_id" : 2, "name" : "Reyansh Sengupta", "degrees" : [ { "level" : "Bachelor",
"major" : "Chemical Engineering", "completion_year" : 2002, "faculty" : "Enginee
ring" } ], "school_email" : "rsengupta2@example.edu" }

嵌套里的嵌套
> db.students3.insert(
...    { "_id" : 1,
...       "grades" : [
...         { type: "quiz", questions: [ 10, 8, 5 ] },
...         { type: "quiz", questions: [ 8, 9, 6 ] },
...         { type: "hw", questions: [ 5, 4, 3 ] },
...         { type: "exam", questions: [ 25, 10, 23, 0 ] },
...
...       ]
...    }
... )
WriteResult({ "nInserted" : 1 })
> db.students3.find()
{ "_id" : 1, "grades" : [ { "type" : "quiz", "questions" : [ 10, 8, 5 ] }, { "ty
pe" : "quiz", "questions" : [ 8, 9, 6 ] }, { "type" : "hw", "questions" : [ 5, 4
, 3 ] }, { "type" : "exam", "questions" : [ 25, 10, 23, 0 ] } ] }

修改全部元素直接用[]
db.students3.update(
   {},
   { $inc: { "grades.$[].questions.$[score]": 2 } },
   { arrayFilters: [  { "score": { $gte: 8 } } ], multi: true}
)

> db.students3.find()
{ "_id" : 1, "grades" : [ { "type" : "quiz", "questions" : [ 12, 10, 5 ] }, { "t
ype" : "quiz", "questions" : [ 10, 11, 6 ] }, { "type" : "hw", "questions" : [ 5
, 4, 3 ] }, { "type" : "exam", "questions" : [ 27, 12, 25, 0 ] } ] }