MongoDB查询-使用组和项目以及匹配和限制展开 [英] MongoDB query - Unwind with group and project and match and limit
问题描述
我具有以下数据类型:
{
"_id" : "HCCIDM1234567A",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,"PremChange" : 1300,"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,"PremChange" : 0,"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,"PremChange" : 0,
"MbrRespChg" : -224,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : 15,
"MbrRespChg" : -224,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : -15,
"MbrRespChg" : -70,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,"PremChange" : -15,
"MbrRespChg" : -77, "NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : 1,
"MbrRespChg" : 77,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,"APTCChange" : 16,
"MbrRespChg" : -200,"NPN" : "U65"
}
]
},
{
"_id" : "HCCIDM1234567B",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,
"PremChange" : 0,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : -15,
"MbrRespChg" : -70,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,
"PremChange" : -15,
"MbrRespChg" : -77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,
"APTCChange" : 16,
"MbrRespChg" : -200,
"NPN" : "U65"
}
]
},
{
"_id" : "HCCIDM1234567C",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,
"PremChange" : 0,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : -15,
"MbrRespChg" : -70,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,
"PremChange" : -15,
"MbrRespChg" : -77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,
"APTCChange" : 16,
"MbrRespChg" : -200,
"NPN" : "U65"
}
]
}
需要满足以下条件:
- startDate $ gte ISODate('2018-01-15T11:13:14.000Z')& endDate $ lte ISODate('2018-02-12T11:13:14.000Z')
-
需要根据以下类别对它们进行分组
PremChange> 10-条记录将在一组中有限制
APTCChange> 10-条记录将限制在一组中 MbrRespChg> 10-条记录将限制在一组中
预期结果:
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
}
--------
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
}
---------
{
id: PremChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
}
-----
{
id: MbrRespChg,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
}
---
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
}
--------
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
}
---------
{
id: PremChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
}
-----
{
id: MbrRespChg,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
}
请建议我如何编写mongodb查询,我写了一个
使用匹配,展开和项目查询,但我没有得到渴望
输出.
您可以尝试以下聚合.不确定为什么需要在这里分组.
$match
将历史记录数组过滤到至少一个元素在指定的日期范围内.
$unwind
来扁平化历史记录数组. /p>
$match
可以过滤元素匹配的历史文档指定的日期范围.
$match
可以过滤历史文档,其中关键值大于10.
db.col.aggregate([
{"$match":{
"history":{
"$elemMatch":{
"startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")},
"endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
}
}
}},
{"$unwind":"$history"},
{"$match":{
"history.startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")},
"history.endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
}},
{"$match":{
"$or":[
{"history.APTCChange":{"$gt":10}},
{"history.PremChange":{"$gt":10}},
{"history.MbrRespChg":{"$gt":10}}
]
}}
])
I have below type of data:
{
"_id" : "HCCIDM1234567A",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,"PremChange" : 1300,"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,"PremChange" : 0,"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,"PremChange" : 0,
"MbrRespChg" : -224,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : 15,
"MbrRespChg" : -224,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : -15,
"MbrRespChg" : -70,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,"PremChange" : -15,
"MbrRespChg" : -77, "NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,"PremChange" : 1,
"MbrRespChg" : 77,"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,"APTCChange" : 16,
"MbrRespChg" : -200,"NPN" : "U65"
}
]
},
{
"_id" : "HCCIDM1234567B",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,
"PremChange" : 0,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : -15,
"MbrRespChg" : -70,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,
"PremChange" : -15,
"MbrRespChg" : -77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,
"APTCChange" : 16,
"MbrRespChg" : -200,
"NPN" : "U65"
}
]
},
{
"_id" : "HCCIDM1234567C",
"RecordT" : "THISAPTC",
"history" : [
{
"startDate" : ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-10T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : -16,
"PremChange" : 0,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-17T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-06T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : -15,
"MbrRespChg" : -70,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-19T11:13:14.000Z"),
"endDate" : ISODate("2018-02-05T11:13:14.000Z"),
"APTCChange" : -10,
"PremChange" : -15,
"MbrRespChg" : -77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-20T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
},
{
"startDate" : ISODate("2018-01-12T11:13:14.000Z"),
"endDate" : ISODate("2018-02-23T11:13:14.000Z"),
"PremChange" : 0,
"APTCChange" : 16,
"MbrRespChg" : -200,
"NPN" : "U65"
}
]
}
Below conditions needs to apply:
- startDate $gte ISODate('2018-01-15T11:13:14.000Z') & endDate $lte ISODate('2018-02-12T11:13:14.000Z')
need to group them based on below category
PremChange>10- records will in one set with limit
APTCChange>10- records will in one set with limit MbrRespChg>10- records will in one set with limit
Expected result:
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
}
--------
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
}
---------
{
id: PremChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
}
-----
{
id: MbrRespChg,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567A',
'startDate': ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
}
---
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-14T11:13:14.000Z"),
"endDate" : ISODate("2018-02-09T11:13:14.000Z"),
"APTCChange" : 1200,
"PremChange" : 1300,
"MbrRespChg" : 100,
"NPN" : "U65"
}
--------
{
id: APTCChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-15T11:13:14.000Z"),
"endDate" : ISODate("2018-02-08T11:13:14.000Z"),
"APTCChange" : 16,
"PremChange" : 0,
"MbrRespChg" : -200,
"NPN" : "U65"
}
---------
{
id: PremChange,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-16T11:13:14.000Z"),
"endDate" : ISODate("2018-02-07T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 15,
"MbrRespChg" : -224,
"NPN" : "U65"
}
-----
{
id: MbrRespChg,
"RecordT" : "THISAPTC",
"hccid':'HCCIDM1234567B',
'startDate': ISODate("2018-01-18T11:13:14.000Z"),
"endDate" : ISODate("2018-02-04T11:13:14.000Z"),
"APTCChange" : 0,
"PremChange" : 1,
"MbrRespChg" : 77,
"NPN" : "U65"
}
please suggest me how i need to write mongodb query, i wrote a
query using match, unwind and project but i am not getting desire
output.
You can try below aggregation. Not sure why you need grouping here.
$match
to filter histories array to where atleast one element is in the specified date range.
$unwind
to flatten the history array.
$match
to filter history documents where element matches the specified date range.
$match
to filter history documents where key values are greater than 10.
db.col.aggregate([
{"$match":{
"history":{
"$elemMatch":{
"startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")},
"endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
}
}
}},
{"$unwind":"$history"},
{"$match":{
"history.startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")},
"history.endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
}},
{"$match":{
"$or":[
{"history.APTCChange":{"$gt":10}},
{"history.PremChange":{"$gt":10}},
{"history.MbrRespChg":{"$gt":10}}
]
}}
])
这篇关于MongoDB查询-使用组和项目以及匹配和限制展开的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!