假设有如下Mongodb表结构数据：
{
"referenceNo": "RRRR-07122023-114511",
"……": "……",
"contractInfo": {
"StartDate": ISODate("2024-01-01T06:10:00.000Z"),
"EndDate": ISODate("2024-01-02T06:10:00.000Z"),
"……": "……",
},
"Info": [
{
"infoId": "1111111111",
"serviceInfo": [
{
"serviceCd": "NC",
"……": 150,
"subInfo": [
{
"subId": "4501679000",
"StartDate": "2024-01-03T06:10:00.000Z",
"EndDate": "2024-01-04T06:10:00.000Z",
"……": "……",
}
]
},
{ }
]
}
],
"……": "……"
}
如何从数据库中查询 referenceNo != "aaa" 且 Info.infoId = 123 且 Info.serviceInfo.serviceCd = "NC" 且 Info.StartDate 和 Info.EndDate 与 "2024-1-1" 和 "2024-12-31" 存在任意交集的数据记录？
答：
Mongodb查询语句如下：
db.xxx.aggregate([
{
$match: {
"referenceNo": { $ne: "aaa" },
"Info.infoId": "123",
"Info.serviceInfo.serviceCd": "NC",
$or: [
{ "Info.StartDate": { $gte: ISODate("2024-01-01T00:00:00.000Z"), $lte: ISODate("2024-01-01T23:59:59.999Z") } },
{ "Info.EndDate": { $gte: ISODate("2024-01-01T00:00:00.000Z"), $lte: ISODate("2024-12-31T23:59:59.999Z") } },
{
"Info.StartDate": { $lt: ISODate("2024-01-01T00:00:00.000") },
"Info.EndDate": { $gt: ISODate("2024-12-31T23:59:59.999") }
},
]
}
},
{ $unwind: "$Info" },
{ $unwind: "$Info.serviceInfo" },
{
$match: {
"referenceNo": { $ne: "aaa" },
"Info.infoId": "123",
"Info.serviceInfo.serviceCd": "NC",
$or: [
{ "Info.StartDate": { $gte: ISODate("2024-01-01T00:00:00.000Z"), $lte: ISODate("2024-01-01T23:59:59.999Z") } },
{ "Info.EndDate": { $gte: ISODate("2024-01-01T00:00:00.000Z"), $lte: ISODate("2024-12-31T23:59:59.999Z") } },
{
"Info.StartDate": { $lt: ISODate("2024-01-01T00:00:00.000") },
"Info.EndDate": { $gt: ISODate("2024-12-31T23:59:59.999") }
},
]
}
},
{
$project: { referenceNo: 1, Info: 1 }
}
])
写成TypeScript语法如下：
async check(refNo: string, infoId: number, serviceCd: string, start: Date, end: Date) {
const query = {
"referenceNo": { $ne: refNo },
"Info.infoId": infoId,
"Info.serviceInfo.serviceCd": serviceCd,
$or: [
{ "Info.StartDate": { $gte: new Date(start), $lte: new Date(end) } },
{ "Info.EndDate": { $gte: new Date(start), $lte: new Date(end) } },
{
"Info.StartDate": { $lt: new Date(start) },
"Info.EndDate": { $gt: new Date(start) },
}
]
}
const result = await this.xxx.aggregate([
{ $match: query },
{ $unwind: "$Info" },
{ $unwind: "$Info.serviceInfo" },
{ $match: query },
{ $project: { referenceNo: 1, Info: 1 } }
]);
return result;
}