MongoDB 4.4 根据结构匹配和分组数组查询

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

MongoDB 4.4 match with group by array query according to structure

问题

这是您的MongoDB聚合管道查询的翻译，该查询可以满足您的需求：

  1. db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       contentType: "Json",
  5.       code: { $in: [200, 404] } // Filter by status codes 200 and 404
  6.     }
  7.   },
  8.   {
  9.     $group: {
  10.       _id: {
  11.         code: "$code", // Group by status code
  12.         fieldName: "$fieldName",
  13.         location: "$location"
  14.       },
  15.       values: {
  16.         $push: "$value"
  17.       }
  18.     }
  19.   },
  20.   {
  21.     $group: {
  22.       _id: "$_id.code", // Group by status code again
  23.       samples: {
  24.         $push: {
  25.           location: "$_id.location",
  26.           field: "$_id.fieldName",
  27.           values: "$values"
  28.         }
  29.       }
  30.     }
  31.   },
  32.   {
  33.     $project: {
  34.       _id: 0,
  35.       code: "$_id",
  36.       samples: {
  37.         $slice: ["$samples", 5] // Limit to the first 5 samples
  38.       }
  39.     }
  40.   },
  41.   {
  42.     $group: {
  43.       _id: null,
  44.       data: {
  45.         $push: "$$ROOT"
  46.       }
  47.     }
  48.   },
  49.   {
  50.     $project: {
  51.       _id: 0,
  52.       data: 1
  53.     }
  54.   }
  55. ])

这个聚合管道查询将根据您的要求对文档进行过滤、分组和限制，并按您所需的结构返回结果。

英文:

Working on mongodb 4.4 version Lets say my collection looks like:

  1.   [
  2.   {
  3.     "_id": ObjectId("6451529ad254d565bd12156b"),
  4.     "contentType": "Json",
  5.     "fieldName": "xxx",
  6.     "method": "GET",
  7.     "value": "Car",
  8.     "location": "header",
  9.     "code": 404
  10.   },
  11.   {
  12.     "_id": ObjectId("6451529ad254d565bd12156d"),
  13.     "contentType": "Json",
  14.     "fieldName": "xxx",
  15.     "method": "GET",
  16.     "value": "Plane",
  17.     "location": "header",
  18.     "code": 404
  19.   },
  20.   {
  21.     "_id": ObjectId("6451529ad254d565bd121570"),
  22.     "contentType": "Json",
  23.     "fieldName": "yyy",
  24.     "method": "GET",
  25.     "value": "Smile",
  26.     "location": "header",
  27.     "code": 404
  28.   },
  29.   {
  30.     "_id": ObjectId("6451529ad254d565bd13156b"),
  31.     "contentType": "Empty",
  32.     "fieldName": "zzz",
  33.     "method": "POST",
  34.     "value": "Train",
  35.     "location": "header",
  36.     "code": 200
  37.   },
  38.   {
  39.     "_id": ObjectId("6451529ad254d5651112156a"),
  40.     "contentType": "Empty",
  41.     "fieldName": "zzz",
  42.     "method": "POST",
  43.     "value": "Player",
  44.     "location": "body",
  45.     "code": 200
  46.   },
  47.   {
  48.     "_id": ObjectId("6451529ad254d565bd12166d"),
  49.     "contentType": "Empty",
  50.     "fieldName": "zzz",
  51.     "method": "POST",
  52.     "value": "Van",
  53.     "location": "body",
  54.     "code": 404
  55.   },
  56.   {
  57.     "_id": ObjectId("6451529ad254d565bd121670"),
  58.     "contentType": "Json",
  59.     "fieldName": "name4",
  60.     "method": "GET",
  61.     "value": "Van",
  62.     "location": "body",
  63.     "code": 200
  64.   }
  65. ]

I'd like to request mongo with

  1. { contentType: Json   codes:  [ 200, 404  ] }

for example i want to filter all documents with contentType:Json and codes 200,404
and group by fieldName with limit 5

the response of this request should look like :

  1. {
  2.   data: [
  3.     {
  4.       code: 200,
  5.       samples: [
  6.         {
  7.           location: “header”,
  8.           “field”: fieldName1,
  9.           values: [
  10.             x1,
  11.             x2, // limit 5
  12.           ]
  13.         },
  14.       {
  15.         location: “headers”,
  16.         field: fieldName2,
  17.         values: [
  18.           y1,
  19.           y2,
  20.           y3 // .. limit 5
  21.       	]
  22.       }
  23.     } ,
  24.     
  25.     {
  26.       code: 404,
  27.       samples: [
  28.         {
  29.           location: “body”,
  30.           “field”: fieldName1,
  31.           values: [
  32.             x1,
  33.             x2 // .. limit 5
  34.           ]
  35.         },
  36.       {
  37.         location: “headers”,
  38.         field: fieldName2,
  39.         values: [
  40.           y1,
  41.           y2,
  42.           y3 .. // limit 5
  43.       	]
  44.       }
  45.     }
  46.   ]
  47. }

tried to do like this but it is not completed https://mongoplayground.net/p/sPk2lViV26l

  1.  db.collection.aggregate([
  2.   {
  3.     $match: {
  4.       contentType: "Json"
  5.     }
  6.   },
  7.   {
  8.     $group: {
  9.       _id: {
  10.         fieldName: "$fieldName",
  11.         location: "$location"
  12.       },
  13.       values: {
  14.         $push: "$value"
  15.       }
  16.     }
  17.   },
  18.   {
  19.     $replaceRoot: {
  20.       newRoot: {
  21.         $mergeObjects: [
  22.           "$_id",
  23.           {
  24.             values: "$values"
  25.           }
  26.         ]
  27.       }
  28.     }
  29.   }
  30. ])

which gives me

  1. [
  2.   {
  3.     "fieldName": "xxx",
  4.     "location": "header",
  5.     "values": [
  6.       "Car",
  7.       "Plane"
  8.     ]
  9.   },
  10.   {
  11.     "fieldName": "name4",
  12.     "location": "body",
  13.     "values": [
  14.       "Van"
  15.     ]
  16.   },
  17.   {
  18.     "fieldName": "yyy",
  19.     "location": "header",
  20.     "values": [
  21.       "Smile"
  22.     ]
  23.   }
  24. ]

but I need it as array with status code as in the response structure

https://mongoplayground.net/p/sPk2lViV26l

答案1

得分: 1

以下是您要翻译的内容：

"我对所需的分组不是完全清楚，但根据我的理解，以下是一种方法。"

  1. db.collection.aggregate([
  2.   {
  3.     "$match": {
  4.       "contentType": "Json",
  5.       "code": {"$in": [200, 404]}
  6.     }
  7.   },
  8.   {
  9.     "$group": {
  10.       "_id": {
  11.         "code": "$code",
  12.         "field": "$fieldName",
  13.         "location": "$location"
  14.       },
  15.       "values": {"$push": "$value"}
  16.     }
  17.   },
  18.   {
  19.     "$group": {
  20.       "_id": "$_id.code",
  21.       "samples": {
  22.         "$push": {
  23.           "field": "$_id.field",
  24.           "location": "$_id.location",
  25.           "values": {"$slice": ["$values", 5]}
  26.         }
  27.       }
  28.     }
  29.   },
  30.   {
  31.     "$group": {
  32.       "_id": null,
  33.       "data": {
  34.         "$push": {
  35.           "code": "$_id",
  36.           "samples": "$samples"
  37.         }
  38.       }
  39.     }
  40.   },
  41.   {"$unset": "_id"}
  42. ])

在mongoplayground.net上尝试它。

英文:

The desired grouping isn't completely clear to me, but using my interpretation, here's one way to do it.

  1. db.collection.aggregate([
  2.   {
  3.     "$match": {
  4.       "contentType": "Json",
  5.       "code": {"$in": [200, 404]}
  6.     }
  7.   },
  8.   {
  9.     "$group": {
  10.       "_id": {
  11.         "code": "$code",
  12.         "field": "$fieldName",
  13.         "location": "$location"
  14.       },
  15.       "values": {"$push": "$value"}
  16.     }
  17.   },
  18.   {
  19.     "$group": {
  20.       "_id": "$_id.code",
  21.       "samples": {
  22.         "$push": {
  23.           "field": "$_id.field",
  24.           "location": "$_id.location",
  25.           "values": {"$slice": ["$values", 5]}
  26.         }
  27.       }
  28.     }
  29.   },
  30.   {
  31.     "$group": {
  32.       "_id": null,
  33.       "data": {
  34.         "$push": {
  35.           "code": "$_id",
  36.           "samples": "$samples"
  37.         }
  38.       }
  39.     }
  40.   },
  41.   {"$unset": "_id"}
  42. ])

Try it on [mongoplayground.net](https://mongoplayground.net/p/Jyiq11sS1es "Click me!").

huangapple
  • 本文由 发表于 2023年5月10日 15:11:36
  • 转载请务必保留本文链接：https://go.coder-hub.com/76215790.html
  • aggregation-framework
  • mongodb
  • mongodb-query
  • mongoose
匿名

发表评论

匿名网友

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

确定