MongoDBにおける集約

15902 ワード

こんにちは、DEVS👋
このブログでは、MongoDBの集約フレームワークの基本を学ぶフィルタリング、ソート、グループ、およびMongoDBの結果を変換します.MongoDBは、データドキュメントを順次処理する一連のオペレーションであるアグリゲーションパイプラインを通して、これらの操作をすべて行うことができます.
実際には、-Mongo playground

入力ドキュメント

[
  {
    "key": 1,
    username: "saurabh",
    age: 18,
    languages: [
      "c",
      "c++"
    ]
  },
  {
    "key": 2,
    username: "leonord",
    age: 22,
    languages: [
      "c",
      "c++",
      "java"
    ]
  },
  {
    "key": 3,
    username: "sheldon",
    age: 14,
    languages: [
      "c",
      "c++",
      "java",
      "python"
    ]
  },
  {
    "key": 4,
    username: "howard",
    age: 32,
    languages: [
      "c",
      "c++",
      "java",
      "python",
      "dart"
    ]
  },
  {
    "key": 5,
    username: "raj",
    age: 5,
    languages: [
      "c",
      "c++",
      "java",
      "python",
      "dart",
      "ts"
    ]
  }
]

1グループの集約=ドキュメントのグループ化と要約に使用します.私たちは_id 有効な式を持つフィールド.

クエリ

db.collection.aggregate([
  {
    $group: {
      _id: "table_stats",
      // Get count of all docs in the collection
      count: {
        $sum: 1
      },
      // Get age stats by grouping age field
      avgAge: {
        $avg: "$age"
      },
      maxAge: {
        $max: "$age"
      },
      minAge: {
        $min: "$age"
      },
      sumAge: {
        $sum: "$age"
      },
      // Get all usernames by grouping username field
      allUsernames: {
        $push: "$username"
      },
      // Get username of first doc
      firstUsername: {
        $first: "$username"
      },
      // Get username of last doc
      lastUsername: {
        $last: "$username"
      }
    }
  }
])

結果

[
  {
    "_id": "table_stats",
    "allUsernames": [
      "saurabh",
      "leonord",
      "sheldon",
      "howard",
      "raj"
    ],
    "avgAge": 18.2,
    "count": 5,
    "firstUsername": "saurabh",
    "lastUsername": "raj",
    "maxAge": 32,
    "minAge": 5,
    "sumAge": 91
  }
]

2これは、フィルタリングによって結果のdocsを減らすために使用されます.

クエリ

// Match all docs where `age` is greater than 20 or equal to 20

db.collection.aggregate([
  {
    "$match": {
      age: {
        $gte: 20
      }
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "key": 2,
    "languages": [
      "c",
      "c++",
      "java"
    ],
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": [
      "c",
      "c++",
      "java",
      "python",
      "dart"
    ],
    "username": "howard"
  }
]

クエリ

// Match all docs that have languages either `python` or `dart` or both

db.collection.aggregate([
  {
    "$match": {
      languages: {
        $in: [
          "python",
          "dart"
        ]
      }
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "key": 3,
    "languages": [
      "c",
      "c++",
      "java",
      "python"
    ],
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": [
      "c",
      "c++",
      "java",
      "python",
      "dart"
    ],
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": [
      "c",
      "c++",
      "java",
      "python",
      "dart",
      "ts"
    ],
    "username": "raj"
  }
]

クエリ

// Match all docs with username `saurabh`

db.collection.aggregate([
  {
    "$match": {
      username: {
        $eq: "saurabh"
      }
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "key": 1,
    "languages": [
      "c",
      "c++"
    ],
    "username": "saurabh"
  }
]

マッチ演算子の一部

$gte = 値が指定された値より大きいか等しい場合にマッチします.

$lte = 値が与えられた値以下の場合にマッチします.

$lt = 値が与えられた値より小さい場合にマッチします.

$gt = 値が与えられた値より大きい場合にマッチします.

$eq = 指定した値に等しい値にマッチします.

$ne = 値と一致しない値にマッチします.

$in = 配列の値のいずれかにマッチします.

$nin = 配列で指定された値にマッチしません.

3 . $ skipと$ limit aggregate =$ skipはスキップするドキュメントの最大数を指定する正の整数をとりますLIMITは、現在の位置から始まる指定された数で、見るドキュメントの数を制限します.

スキップと制限なしで

クエリ

// Get all docs with username lexicographically less than or equal to "saurabh"

db.collection.aggregate([
  {
    "$match": {
      username: {
        $lte: "saurabh"
      }
    }
  },
  // ignore this aggregation, for now, we'll look into it later
  {
    $project: {
      "languages": 0,
      "key": 0,
      "id": 0
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "username": "raj"
  }
]

スキップ= 1とlimit = 2なしで

クエリ

// Skip the first doc ($skip) and return next 2 docs ($limit)

db.collection.aggregate([
  {
    "$match": {
      username: {
        $lte: "saurabh"
      }
    }
  },
  {
    $skip: 1
  },
  {
    $limit: 2
  },
  // ignore this aggregation for now
  {
    $project: {
      "languages": 0,
      "key": 0,
      "id": 0
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "username": "howard"
  }
]

$ sort集約=すべての入力ドキュメントをソートし、ソート順にパイプラインに戻します.

1 = sort ascending, -1 = sort descending.

クエリ

// Get all the docs sorted in ascending order on the `age` field

db.collection.aggregate([
  {
    $sort: {
      age: 1
    }
  },
  // ignore this aggregation for now
  {
    $project: {
      "languages": 0,
      "key": 0,
      "id": 0
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "username": "raj"
  }
]

これは、配列を使用しているドキュメントを展開するために使用されます.

クエリ

db.collection.aggregate([
  {
    $unwind: "$languages"
  },
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "key": 1,
    "languages": "c",
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "key": 1,
    "languages": "c++",
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "key": 2,
    "languages": "c",
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "key": 2,
    "languages": "c++",
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "age": 22,
    "key": 2,
    "languages": "java",
    "username": "leonord"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "key": 3,
    "languages": "c",
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "key": 3,
    "languages": "c++",
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "key": 3,
    "languages": "java",
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "age": 14,
    "key": 3,
    "languages": "python",
    "username": "sheldon"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": "c",
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": "c++",
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": "java",
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": "python",
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "age": 32,
    "key": 4,
    "languages": "dart",
    "username": "howard"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "c",
    "username": "raj"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "c++",
    "username": "raj"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "java",
    "username": "raj"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "python",
    "username": "raj"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "dart",
    "username": "raj"
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "age": 5,
    "key": 5,
    "languages": "ts",
    "username": "raj"
  }
]

クエリ

db.collection.aggregate([
  {
    $unwind: "$languages"
  },
  {
    $match: {
      username: "saurabh"
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "key": 1,
    "languages": "c",
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "age": 18,
    "key": 1,
    "languages": "c++",
    "username": "saurabh"
  }
]

$ project aggregate =キー値を0 ( exclude )または1 ( include )としてコレクションから特定のフィールドを取得する

基本クエリ

db.collection.aggregate([
  {
    $project: {
      username: 1,
      languages: 1
    }
  },
  {
    $unwind: "$languages"
  },
  {
    $match: {
      username: "saurabh"
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "languages": "c",
    "username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "languages": "c++",
    "username": "saurabh"
  }
]

更新列名のクエリ

db.collection.aggregate([
  {
    $project: {
      "system_username": "$username",
      "system_languages": "$languages"
    }
  },
  {
    $unwind: "$system_languages"
  },
  {
    $match: {
      system_username: "saurabh"
    }
  }
])

結果

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "system_languages": "c",
    "system_username": "saurabh"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "system_languages": "c++",
    "system_username": "saurabh"
  }
]

よりクールな記事に続いてください

Hashnode

感謝😎

Reference

この問題について(MongoDBにおける集約), 我々は、より多くの情報をここで見つけました https://dev.to/saurabh619/aggregation-in-mongodb-2b4c

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

Activityのコントロールを巡回

Android学習の道11:Activity