javascript - 如何在猫鼬中加入和运行 NOT EXISTS 查询

Question

我有一个 sql 查询，我需要在 mongoose 聚合中进行转换

SQL：

Select a.participant_id, a.name 
from participant a , settlementAccount b 
Where a.settlementAccount = b.id
and b.account_type = "FundingAgent"
and Not Exists   
(select 1 from settlementAccount c where  b.account_type = "Tracking" and a.id = c.owner_id)

我试图聚合猫鼬，但停留在Not Exists查询的一部分。

猫鼬：

Participant.aggregate([{
  $lookup: {
    from: 'settlementAccount', // collection name in db
    localField: 'settlementAccount',
    foreignField: 'id',
    as: 'settlementOwners'
  }
},
  {
    $match:
      {
        $and:
          [
            {'settlementOwners.account_type': 'FundingAgent'},
            {'settlementOwners.account_type': {$ne: 'Tracking'}},
            {'id': {$ne: 'settlementOwners.owner_id'}}
          ]

      }
  },
  {$project: {_id: '$_id', id: '$id', name: '$name'}}
])

我不知道这是否是正确的实现。有谁知道如何将这些查询组合在一起？

标签： javascriptsqlnode.jsmongodbmongoose