首页 / 技术问答 / Node.js问答列表 / 问答详情

Node.js mongodb首先自连接，然后根据某些条件与另一个表连接

我有两个集合，事务和属性.我希望首先根据输入条件从transaction个集合中获取工具ntId，然后self 联接以从该集合中找到所有相同的工具ntId.请改进我的代码，我在这里绑定了组，但我的查询没有获得数据，所以我从查询中删除了组部分.

db.transaction.aggregate([
          {
            $lookup: {
              from: "transaction",
              localField: "data.instrumentId",
              foreignField: "data.instrumentId",
              as: "s2"
            }
          },
          {
            $match: {
              "data.postingDate": {
                $gte: ISODate("2022-01-01"),
                $lte: ISODate("2022-01-31")
              }
            }
          },
          {
            $project: {
                _id:0,
              "data.instrumentId": 1,
              "data.postingDate": 1
            }
          }
        ")

现在，我希望将此输出数据与第二个集合(attribute)连接起来，该集合基于InstrumentID和postingDate.并希望从属性集合中获取状态，从交易集合中获取金额.

我是Mongo上的新手，所以我有困惑，比如如何首先self 加入，然后加入第二个集合这里是我的代码

db.getCollection("transaction").aggregate([
  {
    $lookup: {
      from: "attributes",
      let: {
        postingDate: "$data.postingDate",
        instrumentId: "$data.instrumentId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$postingDate", "$$postingDate"" },
                { $eq: ["$instrumentId", "$$instrumentId"" }
              "
            }
          }
        }
      ",
      as: "attributes"
    }
  },
  {
                $unwind: "$attributes"
            },
  {
    $match: {
      "data.postingDate": {
        $gte: ISODate("2022-01-01"),
        $lte: ISODate("2022-01-31")
      }
    }
  },
  {
    $group: {
      _id: { 
      instrumentId: "$attributes.instrumentId",
        status: "$attributes.status"
      },
      instrumentIds: { $first: "$data.instrumentId" }
    }
  },
  {
    $project: {
      _id: 0,
      amount: "$data.amount",
      status: "$attributes.status",
      instrumentIds: "$instrumentIds"

    }
  }
")

这是我的交易Collection 

[
{
    "postingDate": "1/1/2023",
    "instrumentId": "Type_1",
    "Amount": " $2,000,000.00 "
},
{
    "postingDate": "1/4/2023",
    "instrumentId": "Type_2",
    "Amount": " $700,000.00 "
},
{
    "postingDate": "1/4/2023",
    "instrumentId": "Type_3",
    "Amount": " $400,000.00 "
},
{
    "postingDate": "2/1/2023",
    "instrumentId": "Type_1",
    "Amount": " $10,000.00 "
},
{
    "postingDate": "2/4/2023",
    "instrumentId": "Type_2",
    "Amount": " $4,000.00 "
},
{
    "postingDate": "2/4/2023",
    "instrumentId": "Type_3",
    "Amount": " $2,000.00 "
},
{
    "postingDate": "3/2/2023",
    "instrumentId": "Type_2",
    "Amount": " $3,900.00 "
},
{
    "postingDate": "3/4/2023",
    "instrumentId": "Type_3",
    "Amount": " $1,950.00 "
},
{,
    "postingDate": "4/1/2023",
    "instrumentId": "Type_2",
    "Amount": " $3,900.00 "
},
{
    "Accounting Period": "Apr-23",
    "postingDate": "4/6/2023",
    "instrumentId": "Type_3",
    "Amount": " $1,950.00 "
},
{
    "postingDate": "5/1/2023",
    "instrumentId": "Type_2",
    "Amount": " $3,900.00 "
},
{
    "postingDate": "5/6/2023",
    "instrumentId": "Type_3",
    "Amount": " $1,950.00 "
},
{
    "postingDate": "5/15/2023",
    "instrumentId": "Type_1",
    "Amount": " $500,000.00 "
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_2",
    "Amount": " $3,500.00 "
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_3",
    "Amount": " $1,700.00 "
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_1",
    "Amount": " $100,000.00 "
}

"

属性集合是

[
{
    "postingDate": "1/1/2023",
    "instrumentId": "Type_1",
    "status": "A"
},
{
    "postingDate": "1/4/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "postingDate": "1/4/2023",
    "instrumentId": "Type_3",
    "status": "A"
},
{
    "postingDate": "2/1/2023",
    "instrumentId": "Type_1",
    "status": "A"
},
{
    "postingDate": "2/4/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "postingDate": "2/4/2023",
    "instrumentId": "Type_3",
    "status": "A",
},
{
    "postingDate": "3/2/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "postingDate": "3/4/2023",
    "instrumentId": "Type_3",
    "status": "N"
},
{,
    "postingDate": "4/1/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "Accounting Period": "Apr-23",
    "postingDate": "4/6/2023",
    "instrumentId": "Type_3",
    "status": "N"
},
{
    "postingDate": "5/1/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "postingDate": "5/6/2023",
    "instrumentId": "Type_3",
    "status": "N"
},
{
    "postingDate": "5/15/2023",
    "instrumentId": "Type_1",
    "status": "N"
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_2",
    "status": "A"
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_3",
    "status": "A"
},
{
    "postingDate": "6/15/2023",
    "instrumentId": "Type_1",
    "status": "A"
}

"

我对‘postingDate=4/30/2023’的预期结果如下所示

    [
  {
    "_id": {
      "postingDate": "4/1/2023",
      "status": "A"
    },
    "allAmountsInstrumentId": [
      {
        "amount": " $700,000.00 ",
        "instrumentId": "Type_2",
        "postingDate": "1/4/2023"
      },
      {
        "amount": " $4,000.00 ",
        "instrumentId": "Type_2",
        "postingDate": "2/4/2023"
      },
      {
        "amount": " $3,900.00 ",
        "instrumentId": "Type_2",
        "postingDate": "3/2/2023"
      },
      {
        "amount": " $3,900.00 ",
        "instrumentId": "Type_2",
        "postingDate": "4/1/2023"
      }
    "
  },
  {
    "_id": {
      "postingDate": "4/6/2023",
      "status": "N"
    },
    "allAmountsInstrumentId": [
      {
        "amount": " $400,000.00 ",
        "instrumentId": "Type_3",
        "postingDate": "1/4/2023"
      },
      {
        "amount": " $2,000.00 ",
        "instrumentId": "Type_3",
        "postingDate": "2/4/2023"
      },
      {
        "amount": " $1,950.00 ",
        "instrumentId": "Type_3",
        "postingDate": "3/4/2023"
      },
      {
        "amount": " $1,950.00 ",
        "instrumentId": "Type_3",
        "postingDate": "4/6/2023"
      }
    "
  }
"

我对‘postingDate=05/31/2023’的预期结果如下

    [
  {
    "_id": {
      "postingDate": "5/15/2023",
      "status": "N"
    },
    "allAmountsInstrumentId": [
      {
        "amount": " $2,000,000.00 ",
        "instrumentId": "Type_1",
        "postingDate": "1/1/2023"
      },
      {
        "amount": " $10,000.00 ",
        "instrumentId": "Type_1",
        "postingDate": "2/1/2023"
      },
      {
        "amount": " $500,000.00 ",
        "instrumentId": "Type_1",
        "postingDate": "5/15/2023"
      }
    "
  },
  {
    "_id": {
      "postingDate": "5/1/2023",
      "status": "A"
    },
    "allAmountsInstrumentId": [
      {
        "amount": " $700,000.00 ",
        "instrumentId": "Type_2",
        "postingDate": "1/4/2023"
      },
      {
        "amount": " $4,000.00 ",
        "instrumentId": "Type_2",
        "postingDate": "2/4/2023"
      },
      {
        "amount": " $3,900.00 ",
        "instrumentId": "Type_2",
        "postingDate": "3/2/2023"
      },
      {
        "amount": " $3,900.00 ",
        "instrumentId": "Type_2",
        "postingDate": "4/1/2023"
      },
      {
        "amount": " $3,900.00 ",
        "instrumentId": "Type_2",
        "postingDate": "5/1/2023"
      }
    "
  },
  {
    "_id": {
      "postingDate": "5/6/2023",
      "status": "N"
    },
    "allAmountsInstrumentId": [
      {
        "amount": " $400,000.00 ",
        "instrumentId": "Type_3",
        "postingDate": "1/4/2023"
      },
      {
        "amount": " $2,000.00 ",
        "instrumentId": "Type_3",
        "postingDate": "2/4/2023"
      },
      {
        "amount": " $1,950.00 ",
        "instrumentId": "Type_3",
        "postingDate": "3/4/2023"
      },
      {
        "amount": " $1,950.00 ",
        "instrumentId": "Type_3",
        "postingDate": "4/6/2023"
      },
      {
        "amount": " $1,950.00 ",
        "instrumentId": "Type_3",
        "postingDate": "5/6/2023"
      },
    "
  }
"

推荐答案

如果我理解正确的话，你想要这样的东西:

db.transactions.aggregate([
  {
    $match: {
      $expr: {
        $lte: [
          {
            $dateFromString: {
              dateString: "$postingDate",
              format: "%m/%d/%Y"
            }
          },
          ISODate("2023-04-30")
        ]
      }
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$instrumentId",
      output: {
        allAmounts: {
          $push: {
            amount: "$Amount",
            postingDate: "$postingDate"
          },
          window: {
            documents: [
              "unbounded",
              "unbounded"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $gte: [
          {
            $dateFromString: {
              dateString: "$postingDate",
              format: "%m/%d/%Y"
            }
          },
          ISODate("2023-04-01")
        ]
      }
    }
  },
  {
    $lookup: {
      from: "attributes",
      let: {
        postingDate: "$postingDate",
        instrumentId: "$instrumentId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$postingDate",
                    "$$postingDate"
                  ]
                },
                {
                  $eq: [
                    "$instrumentId",
                    "$$instrumentId"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            status: 1,
            _id: 0
          }
        }
      ],
      as: "attributes"
    }
  },
  {
    $unwind: "$attributes"
  },
  {
    $group: {
      _id: {
        postingDate: "$postingDate",
        status: "$attributes.status"
      },
      allAmountsInstrumentId: {
        $first: "$allAmounts"
      }
    }
  }
])

看看它在playground example号公路上是如何工作的

Node.js相关问答推荐

如何在node.js中以随机顺序调用函数并按顺序操作

Windows上使用ES6+的OpenAPI规范的Express服务器不接受嵌套路由'

NX无法使用缓存运行根级脚本

NodeJS缓冲区大小逻辑：为什么默认是8KB，而不仅仅是数据大小？

express返回意外的URL

无法使用Sequelize连接AWS RDS

在对象的嵌套数组中使用$lookup，创建多个记录作为响应，mongodb

如何使用 Node.js 连接到 Cloud SQL？

找不到react 模块：错误：默认条件应该是最后一个

登录用户并获取他们的个人资料

aws cdk 2.0 init 应用程序无法构建更漂亮的问题，这来自 jest-snapshot

如何获取在 NodeJS 中执行的脚本的文件名？

在 ExpressJS 中将变量传递给 JavaScript

搜索MongoDB条目的正确方法是'_id'；在 node 中

Node.js、Cygwin 和 Socket.io 走进一家wine 吧……Node.js 抛出 ENOBUFS，所有人都死了

如何让should.be.false语法通过 jslint？

如何在 Joi 字符串验证中使用枚举值

在 Node.js 中获取终端的宽度

node.js 异步库

AWS Lambda：如何将秘密存储到外部 API？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630