如何在Mongo中找到唯一的配对

huangapple go评论56阅读模式
英文:

How to find unique couples in mongo

问题

我将翻译的代码部分保留,以下是翻译好的内容:

我正在插入这些文档:

db.users.insert({user1: 'a', user2: 'b'});
db.users.insert({user1: 'b', user2: 'a'});
db.users.insert({user1: 'c', user2: 'd'});
db.users.insert({user1: 'd', user2: 'c'});

请注意,user1和user2实际上是ObjectId,但我将它们写成单个字符,以更好地解释问题。

我需要选择唯一的行,唯一性由这对用户组成,即两个用户相互连接。

所以在上面的插入中,我创建了两个唯一的对:

  1. 用户 'A' 与用户 'B' 配对
  2. 用户 'C' 与用户 'D' 配对

在MongoDB中是否可以直接实现这一点,还是我需要在我的代码(JavaScript)中使用一些算法?

最终的结果应该是:

[
    {user1: 'A', user2: 'B'},
    {user1: 'C', user2: 'D'}
]

以下结果也可以:

[
    {user1: 'B', user2: 'A'},
    {user1: 'D', user2: 'C'}
]
英文:

I'm inserting these documents:

db.users.insert({user1: 'a',user2: 'b'});
db.users.insert({user1: 'b',user2: 'a'});
db.users.insert({user1: 'c',user2: 'd'});
db.users.insert({user1: 'd',user2: 'c'});

Please note that user1 and user2 are actually ObjectId, but I'm writing them as single char to better explain the issue.

I need to select unique rows, where unicity is given by the couple, i.e. two users are connected to each other.
So in the inserts above Im creating 2 unique couples/pairs:

user 'A' paired with user 'B' 
user 'C' paired with user 'D' 

Is it possible to accomplish this directly in mongo or do I need some algorithm in my code (javascript)?

The final result should be

[
	{user1: 'A',user2: 'B'}
	,{user1: 'C',user2: 'D'}
]

This would also work:

[
	{user1: 'B',user2: 'A'}
	,{user1: 'D',user2: 'C'}
]	

答案1

得分: 1

你可以使用聚合管道。它会按用户对分组文档,并使用 $cond 来确保用户的顺序无关紧要。

db.users.aggregate([
  {
    $group: {
      _id: {
        $cond: [
          { $gt: [ "$user1", "$user2" ] },
          { user1: "$user1", user2: "$user2" },
          { user1: "$user2", user2: "$user1" }
        ]
      },
      count: { $sum: 1 }
    }
  },
  {
    $match: { count: { $gt: 1 } }
  },
  {
    $project: {
      _id: 0,
      user1: "$_id.user1",
      user2: "$_id.user2"
    }
  }
])

https://mongoplayground.net/p/8FLUiMDkG85

英文:

You can use the aggregation pipeline. It groups documents by pair of users and uses $cond to ensure that the order of the users doesn't matter.

db.users.aggregate([
  {
    $group: {
      _id: {
        $cond: [
          { $gt: [ "$user1", "$user2" ] },
          { user1: "$user1", user2: "$user2" },
          { user1: "$user2", user2: "$user1" }
        ]
      },
      count: { $sum: 1 }
    }
  },
  {
    $match: { count: { $gt: 1 } }
  },
  {
    $project: {
      _id: 0,
      user1: "$_id.user1",
      user2: "$_id.user2"
    }
  }
])

https://mongoplayground.net/p/8FLUiMDkG85

答案2

得分: 1

以下是您要的代码部分的翻译:

db.collection.aggregate([
  {
    $group: {
      _id: {
        "$setUnion": [
          [
            "$user1"
          ],
          [
            "$user2"
          ]
        ]
      },
      doc: {
        $first: "$$ROOT"
      }
    }
  }
])

您可以在Mongo Playground上查看示例。

英文:

Without much consideration for special case like only 1 directed relation / more than 2 directed relations(i.e. expected always 2 relations for certain couple). You can simply $group by $setUnion of 2 users and pick the first doc.

db.collection.aggregate([
  {
    $group: {
      _id: {
        "$setUnion": [
          [
            "$user1"
          ],
          [
            "$user2"
          ]
        ]
      },
      doc: {
        $first: "$$ROOT"
      }
    }
  }
])

Mongo Playground

huangapple
  • 本文由 发表于 2023年3月3日 19:02:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/75626245.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定