时间戳

首页 > 解决方案 > 两个集合之间的 MongoDb 查询

node.js - 两个集合之间的 MongoDb 查询

问题描述

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

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 } }
);

我试过的查询：

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"
         }
    }
])

以及更多

标签： node.jsmongodbmongodb-queryaggregation-framework

解决方案

如果有人路过并想检查我是如何使用简单的聚合和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");
      }
    }
  }

推荐阅读