英文:
Querying MongoDB array with Aggregator function: Achieving desired output?
问题
{
"_id": "act_8347100017075469",
"firstName": "Akki",
"lastName": "Dlast",
"accountNumber": "12397124",
"feature": {
"feature-name1": {
"baseCode": "bc1",
"serviceCode": [
"SC1",
"SC2",
"SC3"
]
},
"feature-name2": {
"baseCode": "bc2",
"serviceCode": [
"SC21",
"SC22"
]
},
"feature-name3": {
"baseCode": "bc3",
"serviceCode": [
"SC41",
"SC42",
"SC43"
]
}
}
}
Account No feature-name, base-code, svc-code
12397124, feature-name1, bc1, SC1
12397124, feature-name1, bc1, SC2
12397124, feature-name1, bc1, SC3
12397124, feature-name2, bc2, SC21
12397124, feature-name2, bc2, SC22
12397124, feature-name3, bc3, SC41
12397124, feature-name3, bc3, SC42
12397124, feature-name3, bc3, SC43
db.collection.aggregate([
{
$match: {
"feature.feature-name1.baseCode": {
$exists: true
}
}
},
{
$unwind: "$feature.feature-name1.serviceCode"
},
{
$project: {
accountNumber: 1,
featureName: "feature-name1",
baseCode: "$feature.feature-name1.baseCode",
serviceCode: "$feature.feature-name1.serviceCode"
}
}
])
英文:
For my problem i have below sample database record & in output need to develop result as mentioned in output desk. data. I am very new to mongoDb but trying $match
, $aggregator
, $projection
to develop expected result. Any suggestion help would be really appreciated
{
"_id": "act_8347100017075469",
"firstName": "Akki",
"lastName": "Dlast",
"accountNumber": "12397124",
"feature": {
"feature-name1": {
"baseCode": "bc1",
"serviceCode": [
//numb of elements & name of elements in servicesCodes are dynamic
"SC1",
"SC2",
"SC3"
]
},
"feature-name2": {
"baseCode": "bc2",
"serviceCode": [
//numb of elements & name of elements in servicesCodes are dynamic
"SC21",
"SC22"
]
},
"feature-name3": {
"baseCode": "bc3",
"serviceCode": [
"SC41",
"SC42",
"SC43"
]
}
}
}
output
Account No feature-name, base-code, svc-code
12397124, feature-name1, bc1, SC1
12397124, feature-name1, bc1, SC1
12397124, feature-name1, bc1, SC3
12397124, feature-name2, bc2, SC21
12397124, feature-name2 bc2, SC21
12397124, feature-name3, bc3, SC41
12397124, feature-name3 bc3, SC42
12397124, feature-name3, bc3, SC43
Attempt at aggregation pipeline (mongoplayground.net example):
db.collection.aggregate([
{
$match: {
"feature.feature-name1.baseCode": {
$exists: true
}
}
},
{
$project: {
accountNbr: 1,
"feature": "feature-name1",
"baseCode": "$feature.feature-name1.baseCode"
}
}
])
... but this only returns part of the required output.
[
{
"_id": "act_8347100017075469",
"baseCode": "bc1",
"feature": "feature-name1"
}
]
答案1
得分: 0
这是一种你可以实现的方法。
db.collection.aggregate([
{
"$set": {
"feature": {
"$objectToArray": "$feature"
}
}
},
{
"$unwind": "$feature"
},
{
"$unwind": "$feature.v.serviceCode"
},
{
"$sort": {
"accountNumber": 1,
"feature.k": 1,
"feature.v.baseCode": 1,
"feature.v.serviceCode": 1
}
},
{
"$project": {
"_id": 0,
"Account No": "$accountNumber",
"feature-name": "$feature.k",
"base-code": "$feature.v.baseCode",
"svc-code": "$feature.v.serviceCode"
}
}
])
你可以在mongoplayground.net上尝试它。
英文:
Here's one way you could do it.
db.collection.aggregate([
{
"$set": {
"feature": {
"$objectToArray": "$feature"
}
}
},
{
"$unwind": "$feature"
},
{
"$unwind": "$feature.v.serviceCode"
},
{
"$sort": {
"accountNumber": 1,
"feature.k": 1,
"feature.v.baseCode": 1,
"feature.v.serviceCode": 1
}
},
{
"$project": {
"_id": 0,
"Account No": "$accountNumber",
"feature-name": "$feature.k",
"base-code": "$feature.v.baseCode",
"svc-code": "$feature.v.serviceCode"
}
}
])
Try it on [mongoplayground.net](https://mongoplayground.net/p/sNxPX6hvCZ4 "Click me!").
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论