mongodb first self join and then join with another table based on some conditions(MongoDB先自联接，然后根据某些条件与另一个表联接)-6ren

mongodb first self join and then join with another table based on some conditions(MongoDB先自联接，然后根据某些条件与另一个表联接)

转载作者：bug小助手更新时间：2023-10-25 21:17:45

I have two collections, transaction and attributes. I want first to get the instrumntIds based on input conditions from transaction collection and then self join to find all the same instrumntIds from this collection. please improve my code, here I tied group but then my query is not getting the data so I removed group part from the query.

我有两个集合，事务和属性。我希望首先根据输入条件从事务集合中获取工具ntID，然后自我联接以从该集合中找到所有相同的工具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
            }
          }
        ])

now I want to join this output data with second collection(attribute) based on instrumentId and postingDate. and want to get status from the attribute collection and amount from transaction collection.

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

I am new on mongo so I have confusion like how to self join first and then join with second collection here is my code

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

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"

    }
  }
])

Here is my transaction 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 "
}

]

and attribute collection is

属性集合是

[
{
    "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"
}

]

my expected result for the 'postingDate = 4/30/2023' will be look like

我对‘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"
      }
    ]
  }
]

my expected result for the 'postingDate = 05/31/2023' will be look like

我对‘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"
      },
    ]
  }
]

更多回答

Please add what is the expected output.

请添加预期产量是多少。

@CharchitKapoor I updated the collections and mentioned the expected output.

@ChArchitKapoor我更新了集合，并提到了预期的输出。

consider change your structure of a transaction to something like: {"postingDate": ISODate("2023-01-01"), "instrumentId": "Type_1", "Amount": 2000000, "currency": "USD"}

考虑将您的交易结构更改为：{“postingDate”：isodate(“2023-01-01”)，“InstrumentID”：“Type_1”，“Amount”：2000000，“Currency”：“U.S.”}

Does this answers it? You can use $setWindowFields to sum the amounts per ` instrumentId`

这能回答这个问题吗？您可以使用$setWindowFields对每个`InformentId`的金额求和

Does this answers your question?

这回答了你的问题吗？

优秀答案推荐

If I understand correctly, you want something like this:

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

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

See how it works on the playground example

看看它在操场上是如何工作的示例

For mongoDB versions older than 5.0, you can replace the $setWindowFields with a $lookup stage:

对于早于5.0的MongoDB版本，可以将$setWindowFields替换为$lookup阶段：

 {$lookup: {
      from: "transactions",
      localField: "instrumentId",
      foreignField: "instrumentId",
      pipeline: [
        {$match: {$expr: {$lte: [
                {$dateFromString: {
                    dateString: "$postingDate",
                    format: "%m/%d/%Y"
                }},
                ISODate("2023-04-30")
        ]}}},
        {$project: {
            amount: "$Amount",
            postingDate: 1,
            _id: 0
        }}
      ],
      as: "allAmounts"
  }},

See how it works on the playground example

看看它在操场上是如何工作的示例

更多回答

I am using mongo version 4.4.11 and $setWindowFields is not working on it. is there any alternative for it

我使用的是Mongo版本4.4.11，而$setWindowFields没有在它上面工作。有什么替代办法吗？

$lookup is an alternative

$lookup是一种替代方案

{ $group: { _id: { instrumentId: "$data.instrumentId", postingDate: "$data.postingDate" }, allAmounts: { $push: { amount: "$data.amount", postingDate: "$data.postingDate", instrumentId: "$data.instrumentId" } } } }, I changed setWindowFields to this logic but the self join part is not working

{$group：{_id：{InstrumentID：“$data.InstrumentID”，postingDate：“$data.postingDate”}，allAmount：{$PUSH：{Amount：“$data.mount”，postingDate：“$data.postingDate”，InstrumentID：“$data.InformentID”}}}，我将setWindowFields更改为此逻辑，但自联接部分不起作用

I am trying to remove $setWindowFields block, because my mongo version is old and it is not supporting the $setWindowFileds, please any help for this

我正在尝试删除$setWindowFields块，因为我的mongo版本是旧的，并且它不支持$setWindowFilez，请对此提供帮助

Updated the answer accordingly

相应地更新了答案

ios - 是否需要使用无用的 object.self.self.. 或 Class.self.self.. ？
让我们写一个简单的类在我的脑海中解释: class SomeClass { var happyToUsed = 10 } 并创建一个对象 let someObject = SomeClass(
rust - 什么时候在方法中使用 self、&self 和 &mut self？
采用 self 的方法与采用 &self 甚至 &mut self 的方法有什么区别？例如 impl SomeStruct { fn example1(self) { } fn ex
python - 当在另一个函数(self)中声明函数(self)时，“self”丢失了一些东西
请观察以下代码(Win10上的python 3.6，PyCharm)，函数thread0(self)作为线程成功启动，但是 thread1(self)似乎与thread0(self)不同已设置。 se
javascript - JavaScript 中 self.self == self check 的目标是什么？
backbone.js 开始于: //Establish the root object, `window` (`self`) in the browser, or `global` on the s
objective-c - 是否执行 self = self init;返回 self ； Objective-C 中的效果与 swift 中 self.init() 的效果相同吗？
做的事: self = self.init; return self; 在 Objective-C 中具有相同的效果: self.init() 快速？例如，在这种情况下: else if([form
ios - 如果他们引用的对象被释放，守卫是否可以在使用 [weak self] 的闭包中让 `self` = self 导致崩溃？
我查看了关于堆栈溢出的一些关于使用[weak self]和[unowned self]的问题的评论。我需要确保我理解正确。我正在使用最新的 Xcode - Xcode 13.4，最新的 macOS
ios - 如何纠正在 "self' 调用或赋值给 'self.init' 之前使用 'self' 的错误？
我面临在以下模型类代码中的 self.init 调用或分配给 self 之前使用 self 的错误tableview单元格项目，它发生在我尝试获取表格单元格项目的文档ID之后。应该做什么？请推荐。
ios - 当 self 在转义闭包中很弱时，在 self 函数中调用 self 参数
晚上好。我对在 Swift 中转义(异步)闭包有疑问，我想知道哪种方法是解决它的最佳方法。有一个示例函数。 func exampleFunction() { functionWithEsca
swift - 我们是否需要在每个嵌套闭包内重复 `guard let self = self else { return }` 以保持强大的 self ？
我需要在内心深处保持坚强的自我。我知道声明[weak self]就够了外封闭仅一次。但是guard let self = self else { return }呢？，是否也足以为外部闭包声明一
rust - 由于多次使用 `use std::{fs::self, io::self}`，无法写入 `self`
代码 use std::{ fs::self, io::self, }; fn rmdir(path: impl AsRef) -> io::Result { fs::remo
ios - 在 'self' 调用之前使用的 'self.init' 错误或在不同模块的 init 上分配给 'self'
我检查了共享相同主题的问题，但没有一个解决我遇到的这种奇怪行为: 说我有一个简单的老学校struct : struct Person { var name: String var age:
python - Flask 邮件错误 : headers = [self. 发件人，self.reply_to] + self.recipients
我应该解释为什么我的问题不是重复的:TypeError: can only concatenate list (not “str”) to list ...所以它不是重复的，因为该帖子处理代码中出现的
Scala:非法继承； self 类型 Y 不符合 X 的 self 类型 SELF
我有一个 trait，它接受一个类型参数，我想说实现这个 trait 的对象也会符合这个类型参数(使用泛型，为了 Java 的兼容性) 以下代码: trait HandleOwner[SELF
jquery - 为什么要将 $ 与 $self 和 $body 放在一起？并且 self 与 $self 相同
这个问题在这里已经有了答案: Why would a JavaScript variable start with a dollar sign? [duplicate] (16 个答案) 关闭 8
javascript - 'self.fn.apply(self, message)' 和 'self.fn(message)' 之间有什么不同，为什么使用第一种方法？
我总是找到一些类似的代码newPromise.promiseDispatch.apply(newPromise, message)，我不明白为什么不使用newPromise.promiseDispat
python - 如何避免 __init__ 中的 "self.x = x; self.y = y; self.z = z"模式？
我看到类似的模式 def __init__(self, x, y, z): ... self.x = x self.y = y self.z = z ... 非
Doctrine2 Self Join，如何在没有关系列的情况下加入 self？
mysql查询示例: SELECT a1.* FROM agreement a1 LEFT JOIN agreement a2 on a1.agreementType = a2.agreementTy
iphone - self.delegate = self；这样做有什么问题吗？
self.delegate = self; 这样做有什么问题吗？正确的做法是什么？谢谢，尼尔。代码: (UITextField*)initWith:(id)sender:(float)X:(flo
swift - 为什么在类中需要声明 self 的结构中不需要声明 self ？
为什么要声明self在类中需要的结构中不需要？我不知道是否还有其他例子说明了这种情况，但在转义闭包的情况下，确实如此。如果闭包是非可选的(因此是非转义的)，则不需要声明 self在两者中的任何一个。
rust - &self 和 self 有什么区别？
这个问题已经有答案了: What does the ampersand (&) before `self` mean in Rust? (1 个回答) 已关闭去年。我不清楚 self 之间有什么区别

bug小助手

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mongodb first self join and then join with another table based on some conditions(MongoDB先自联接，然后根据某些条件与另一个表联接)