使用聚合函数查询MongoDB数组:如何实现所需的输出?

huangapple go评论55阅读模式
英文:

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!").

huangapple
  • 本文由 发表于 2023年5月29日 14:39:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76355156.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定