mongodb-17.aggregate，update，updateMany

练习1 多个doc聚合展示

准备数据

db.orders.insertMany([
   { "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "item" : "pecans", "price" : 20, "quantity" : 1 },
])

db.inventory.insertMany([
   { "sku" : "almonds", "description": "product 1", "instock" : 120 },
   { "sku" : "cashews", "description": "product 3", "instock" : 60 },
   { "sku" : "pecans", "description": "product 4", "instock" : 70 }
])

聚合查询

db.orders.aggregate([
   { $match: { price: { $lt: 15 } } },
   { $lookup: {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
   } },
   { $sort: { price: 1 } },
])

结果

{ _id: ObjectId("63ad68ac25f744b34698724d"),
  item: 'almonds',
  price: 12,
  quantity: 2,
  inventory_docs: 
   [ { _id: ObjectId("63ad68ed25f744b34698724f"),
       sku: 'almonds',
       description: 'product 1',
       instock: 120 } ] }

练习2 练习update $$NOW

db.students.insertMany( [
   { _id: 1, test1: 95, test2: 92, test3: 90, modified: new Date("01/05/2020") },
   { _id: 2, test1: 98, test2: 100, test3: 102, modified: new Date("01/05/2020") },
   { _id: 3, test1: 95, test2: 110, modified: new Date("01/04/2020") }
] )

修改

db.students.updateOne( { _id: 3 }, [ { $set: { "test3": 98, modified: "$$NOW"} } ] )

修改之后

db.students.find()
{ _id: 1,
  test1: 95,
  test2: 92,
  test3: 90,
  modified: 2020-01-04T16:00:00.000Z }
{ _id: 2,
  test1: 98,
  test2: 100,
  test3: 102,
  modified: 2020-01-04T16:00:00.000Z }
{ _id: 3,
  test1: 95,
  test2: 110,
  modified: 2022-12-29T10:29:58.184Z,
  test3: 98 }

the pipeline consists of a $set stage which adds the test3 field (and sets its value to 98) to the document and sets the modified field to the current datetime. The operation uses the aggregation variable NOW for the current datetime. To access the variable, prefix with $$ and enclose in quotes.

练习3 新增字段

db.students2.insertMany( [
   { "_id" : 1, quiz1: 8, test2: 100, quiz2: 9, modified: new Date("01/05/2020") },
   { "_id" : 2, quiz2: 5, test1: 80, test2: 89, modified: new Date("01/05/2020") },
] )

db.students2.updateMany( {},
  [
    { $replaceRoot: { newRoot:
       { $mergeObjects: [ { quiz1: 0, quiz2: 0, test1: 0, test2: 0 }, "$$ROOT" ] }
    } },
    { $set: { modified: "$$NOW"}  }
  ]
)

结果

{ _id: 1,
  quiz1: 8,
  quiz2: 9,
  test1: 0,
  test2: 100,
  modified: 2022-12-29T10:34:21.084Z }
{ _id: 2,
  quiz1: 0,
  quiz2: 5,
  test1: 80,
  test2: 89,
  modified: 2022-12-29T10:34:21.084Z }

练习4 $avg $trunc $switch

db.students3.insertMany( [
   { "_id" : 1, "tests" : [ 95, 92, 90 ], "modified" : ISODate("2019-01-01T00:00:00Z") },
   { "_id" : 2, "tests" : [ 94, 88, 90 ], "modified" : ISODate("2019-01-01T00:00:00Z") },
   { "_id" : 3, "tests" : [ 70, 75, 82 ], "modified" : ISODate("2019-01-01T00:00:00Z") }
] );

db.students3.updateMany(
   { },
   [
     { $set: { average : { $trunc: [ { $avg: "$tests" }, 0 ] }, modified: "$$NOW" } },
     { $set: { grade: { $switch: {
                           branches: [
                               { case: { $gte: [ "$average", 90 ] }, then: "A" },
                               { case: { $gte: [ "$average", 80 ] }, then: "B" },
                               { case: { $gte: [ "$average", 70 ] }, then: "C" },
                               { case: { $gte: [ "$average", 60 ] }, then: "D" }
                           ],
                           default: "F"
     } } } }
   ]
)

结果

{ _id: 1,
  tests: [ 95, 92, 90 ],
  modified: 2022-12-29T10:38:09.639Z,
  average: 92,
  grade: 'A' }
{ _id: 2,
  tests: [ 94, 88, 90 ],
  modified: 2022-12-29T10:38:09.639Z,
  average: 90,
  grade: 'A' }
{ _id: 3,
  tests: [ 70, 75, 82 ],
  modified: 2022-12-29T10:38:09.639Z,
  average: 75,
  grade: 'C' }

练习5 数组追加

db.students4.insertMany( [
  { "_id" : 1, "quizzes" : [ 4, 6, 7 ] },
  { "_id" : 2, "quizzes" : [ 5 ] },
  { "_id" : 3, "quizzes" : [ 10, 10, 10 ] }
] )

db.students4.updateOne( { _id: 2 },
  [ { $set: { quizzes: { $concatArrays: [ "$quizzes", [ 8, 6 ]  ] } } } ]
)

结果

{ _id: 1, quizzes: [ 4, 6, 7 ] }
{ _id: 2, quizzes: [ 5, 8, 6 ] }
{ _id: 3, quizzes: [ 10, 10, 10 ] }

练习6 $map $multiply运算

db.temperatures.insertMany( [
  { "_id" : 1, "date" : ISODate("2019-06-23"), "tempsC" : [ 4, 12, 17 ] },
  { "_id" : 2, "date" : ISODate("2019-07-07"), "tempsC" : [ 14, 24, 11 ] },
  { "_id" : 3, "date" : ISODate("2019-10-30"), "tempsC" : [ 18, 6, 8 ] }
] )

db.temperatures.updateMany( { },
  [
    { $addFields: { "tempsF": {
          $map: {
             input: "$tempsC",
             as: "celsius",
             in: { $add: [ { $multiply: ["$$celsius", 9/5 ] }, 32 ] }
          }
    } } }
  ]
)

新增字段tempsF，该字段是将tempsC的值乘以9/5,然后再加上32

结果

{ _id: 1,
  date: 2019-06-23T00:00:00.000Z,
  tempsC: [ 4, 12, 17 ],
  tempsF: [ 39.2, 53.6, 62.6 ] }
{ _id: 2,
  date: 2019-07-07T00:00:00.000Z,
  tempsC: [ 14, 24, 11 ],
  tempsF: [ 57.2, 75.2, 51.8 ] }
{ _id: 3,
  date: 2019-10-30T00:00:00.000Z,
  tempsC: [ 18, 6, 8 ],
  tempsF: [ 64.4, 42.8, 46.4 ] }

扩展讲解:http://www.manongjc.com/detail/30-qjvgvieiqjdukyu.html

https://www.mongodb.com/docs/manual/reference/method/js-collection/