mongodb - 如何在 MongoDB 中获取汇总报告
问题描述
我有以下结构的文件。
样本文件:
{
"location" : {
"street" : {
"number" : 9360,
"name" : "Travessa dos Açorianos"
},
"coordinates" : {
"latitude" : -69.3199,
"longitude" : 154.0748
},
"timezone" : {
"offset" : "-8:00",
"description" : "Pacific Time (US & Canada)"
},
"city" : "Divinópolis",
"state" : "Espírito Santo",
"country" : "Brazil",
"postcode" : "23089"
},
"gender" : "male",
"login" : {
"uuid" : "b932ef44-2afb-4b72-a1c5-47b2263bbab2",
"username" : "goldenfish516",
"password" : "sharks",
"salt" : "O7UQElyh",
"md5" : "9f217bf6d5d8576703dbd38e864f709f",
"sha1" : "22a4dbba7eea9b47537ed9931b907ceae10f4de7",
"sha256" : "2bcf93087fcd2ecc623702ad5727e5a24f33ee343c1871028a7950e770c53ee4"
},
"dob" : {
"date" : ISODate("1983-09-10T18:56:41.614Z"),
"age" : 37 // get report from based on age of male and female using nat field
},
"registered" : {
"date" : ISODate("2005-04-10T08:57:39.425Z"),
"age" : 15
},
"nat" : "NL",
}
我想生成汇总报告,如何使用 mongo 根据年龄和国籍获取男性和女性的记录。
所需输出:
解决方案
您可以尝试以下聚合查询:
db.collection.aggregate([
/** Group on 'nat' field & sum no.of doc's based on age to specific category using conditional check */
{
$group: {
_id: "$nat",
"0-30": {
$sum: {
$cond: [
{ $and: [{ $gte: ["$dob.age", 0] }, { $lt: ["$dob.age", 30] }] },
1,
0
]
}
},
"30-50": {
$sum: {
$cond: [
{ $and: [{ $gte: ["$dob.age", 30] }, { $lt: ["$dob.age", 50] }] },
1,
0
]
}
},
"50plus": { $sum: { $cond: [{ $gte: ["$dob.age", 50] }, 1, 0] } }
}
},
/** Group on nationality & push male & female objects into respective arrays */
{
$group: {
_id: "$_id.nationality",
male: {
$push: {
$cond: [
{ $eq: ["$_id.gender", "male"] },
{
"0-30": "$$ROOT.0-30",
"30-50": "$$ROOT.30-50",
"50plus": "$$ROOT.50plus"
},
"$$REMOVE"
]
}
},
female: {
$push: {
$cond: [
{ $eq: ["$_id.gender", "female"] },
{
"0-30": "$$ROOT.0-30",
"30-50": "$$ROOT.30-50",
"50plus": "$$ROOT.50plus"
},
"$$REMOVE"
]
}
}
}
},
{
$project: {
_id: 0,
nationality: "$_id",
female: { $arrayElemAt: ["$female", 0] }, // Get object from array
male: { $arrayElemAt: ["$male", 0] }
}
}
]);
测试: MongoDB-游乐场
推荐阅读
- devops - 为网站/移动应用寻找最佳架构和托管解决方案
- bitbucket-pipelines - Bitbucket 自托管运行器在步骤设置期间失败
- javascript - 反应,获取未定义的 accessToken
- reactjs - 从函数内部调用时 setState 不更新
- javascript - 反应本机错误“`filePath`需要一个字符串值”
- python - 在 Python 中读取文件的每 N 个字节
- visual-studio-code - 扩展侧边栏中的角星号是什么意思?
- javascript - 如何在 sequlize 中做一个简单的 LEFT JOIN?
- reactjs - react-hook-form yup 解析器,reactStrap 解决子组件错误的问题
- java - Firebase 实时数据库未更新 // 数据未添加到 Firebase 实时数据库