MongoDb在两个集合之间的查询

Question

我有这个查询，应该合并两个表并返回结果

db.purchasefromobjectmodel.aggregate([
{
"$lookup":{
"from":"amazonmodels",
"localField":"receiptId",
"foreignField":"receiptId",
"as" :"results"
}])

我如何编辑它，使它返回给我的对象，其中第一个表的purchaseDate等于第二个表的数据(或接近于微小的差异>24hrs)，对象通过receiptId链接

编辑:

const purchaseFromAppObjectModelSchema = new mongoose.Schema({
receiptId: String,
sku: String,
itemType: String,
purchaseDate: { type: Date, default: Date.now },
UserData: { type: [userDataModel] },
idfa: String,
status: String,
appName: String,
trafficSource: String,
trafficName: String,
appVersion: String,
deviceName: String,
deviceBrand: String,
});

const amazonSchema = new mongoose.Schema(
{
autoRenewing: Boolean,
betaProduct: Boolean,
cancelDate: Number,
cancelReason: Number,
deferredDate: String,
deferredSku: String,
freeTrialEndDate: Number,
gracePeriodEndDate: Number,
parentProductId: String,
productId: String,
productType: String,
purchaseDate: Number,
quantity: Number,
receiptId: String,
renewalDate: Number,
term: String,
termSku: String,
testTransaction: Boolean,
},
{ timestamps: true, toObject: { virtuals: true }, toJSON: { virtuals: true } }
);

查询I tried:

let result = await purchaseFromAppObjectModel.aggregate([
{
$lookup: {
from: "amazonmodels",
localField: "purchaseDate",
foreignField: "purchaseDate",
as: "purchaseDateInfo",
},
},
// {
//   $replaceRoot: {
//     newRoot: {
//       $mergeObjects: [{ $arrayElemAt: ["$purchaseDateInfo", 0] }, "$$ROOT"],
//     },
//   },
// },
// { $project: { purchaseDateInfo: 0 } },
]);

This one merges the collections with same receipt id
{
$lookup: {
from: "amazonmodels",
localField: "receiptId",
foreignField: "receiptId",
as: "purchaseDateInfo",
},
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [{ $arrayElemAt: ["$purchaseDateInfo", 0] }, "$$ROOT"],
},
},
},
{ $project: { purchaseDateInfo: 0 } },
]);

let result = await purchaseFromAppObjectModel.aggregate([
{$match : { purchaseDate : createdAt } } ,
{
$lookup: {
from: "amazonmodels",
localField: "purchaseDate",
foreignField: "purchaseDate",
as: "purchaseDateInfo",
},
},
// {
//   $replaceRoot: {
//     newRoot: {
//       $mergeObjects: [{ $arrayElemAt: ["$purchaseDateInfo", 0] }, "$$ROOT"],
//     },
//   },
// },
// { $project: { purchaseDateInfo: 0 } },
]);

我认为这是我应该试着嘲笑的人:

db.orders.aggregate([
{
$lookup:
{
from: "warehouses",
let: { order_item: "$item", order_qty: "$ordered" },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ "$stock_item",  "$$order_item" ] },
{ $gte: [ "$instock", "$$order_qty" ] }
]
}
}
},
{ $project: { stock_item: 0, _id: 0 } }
],
as: "stockdata"
}
}
])

和更多

Answer 1

试试这个:

db.purchaseFromApp.aggregate([
{
$lookup: {
from: "amazonmodels",
let: {
purchaseDate: {
$dateFromString: {
dateString: { $toString: "$purchaseDate" },
format: "$Y-%m-%d"
}
},
receiptId: "$receiptId"
},
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$receiptId", "$$receiptId"] },
{
$eq: [
{
$dateFromString: {
dateString: { $toString: "$purchaseDate" },
format: "$Y-%m-%d"
}
},
"$$purchaseDate"
]
}
]
}
}
}
],
as: "purchaseDateInfo"
}
},
// Add other stages
]);

Answer 2

如果有人经过并想检查我是如何使用简单的聚合和nodejs这样做的:

//first the query
let result = await purchaseFromAppObjectModel.aggregate([
{
$lookup: {
from: "amazonmodels",
localField: "receiptId",
foreignField: "receiptId",
as: "amazonResults",
},
},
]);

//Then looping
for (var i = 0; i <= result.length - 1; i++) {
let objResult = result[i];
console.log(
"this is the result index : ",i,"this is the size :",objResult.amazonResults.length);
let purchaseDate = objResult.purchaseDate;
if (objResult.amazonResults != 0) {
let createdAt = objResult.amazonResults[0].createdAt;
const diffTime = Math.abs(createdAt - purchaseDate);
console.log(diffTime);
if (diffTime < 5000) {
console.log("purchase date  " + purchaseDate);
console.log("createdAt  " + createdAt);
console.log("same date");
} else {
console.log(" different dates");
}
}
}