php - mongodb 按多个键值分组，反之亦然

Question

我有一个包含以下数据的用户集合

    [
  {
    "user_id": "5625c95ac2d34f27148b64fa",
    "friend_id": "561f40bac2d34f17148b462c"
  },
  {
    "user_id": "562744ccc2d34f27148b6eb7",
    "friend_id": "561f40bac2d34f17148b462c"
  },
  {
    "user_id": "56248eb9c2d34f2f148b5a18",
    "friend_id": "561f40bac2d34f17148b462c"
  },
  {
    "user_id": "561f40bac2d34f17148b462c",
    "friend_id": "561f3e06c2d34f27148b45f6"
  },
  {
    "user_id": "561f40bac2d34f17148b462c",
    "friend_id": "5620de97c2d34f2f148b578f"
  },
  {
    "user_id": "56276b52c2d34f27148b7128",
    "friend_id": "561f40bac2d34f17148b462c"
  },
  {
    "user_id": "561f40bac2d34f17148b462c",
    "friend_id": "56276b52c2d34f27148b7128"
  }
]

我需要获取 user_id 和 friend_id 组合不重复的文档。即在上面的示例中，最后两个文档 user_id 在下一个文档的 friend_id 中重复。

我尝试使用 mongo 聚合和分组，但无法减少它。

Answer 1

为此，您基本上需要将 user_id 和 friend_id 值组合成唯一排序的组合。这意味着为包含这些成员的每个文档创建一个数组并对该数组进行排序，以便顺序始终相同。

然后您可以在排序后的数组内容上$group 来查看哪些文档包含相同的组合，然后只返回那些不共享相同组合的文档。

这导致了这个聚合语句:

db.collection.aggregate([
    { "$project": {
        "user_id": 1,
        "friend_id": 1,
        "combined": {
            "$map": {
                "input": ["A","B"],
                "as": "el",
                "in": {
                    "$cond": [ 
                        { "$eq": [ "$$el", "A" ] },
                        "$user_id",
                        "$friend_id"
                    ]
                }
            }
        }            
    }},
    { "$unwind": "$combined" },
    { "$sort": { "combined": 1 } },
    { "$group": {
        "_id": "$_id",
        "combined": { "$push": "$combined" },
        "user_id": { "$first": "$user_id" },
        "friend_id": { "$first": "$friend_id"  }
    }},
    { "$group": {
        "_id": "$combined",
        "docs": { "$push": {
            "_id": "$_id",
            "user_id": "$user_id",
            "friend_id": "$friend_id"
        }}
    }},
    { "$redact": {
        "$cond": {
            "if": { "$ne": [{ "$size": "$docs" }, 1] },
            "then": "$$PRUNE",
            "else": "$$KEEP"
        }
    }}
])

laravel 的 PHP 翻译意味着需要从管理器访问原始集合对象，其中“collection”是 MongoDB 中集合的实际名称:

$result = DB::collection("collection")->raw(function($collection) {
    return $collection->aggregate(
        array(
            array(
                '$project' => array(
                    'user_id' => 1,
                    'friend_id' => 1,
                    'combined' => array(
                        '$map' => array(
                            'input' => array("A","B"),
                            'as' => 'el',
                            'in' => array(
                                '$cond' => array(
                                    array( '$eq' => array( '$el', 'A' ) ),
                                    '$user_id',
                                    '$friend_id'
                                )
                            )
                        )
                    )
                )
            ),
            array( '$unwind' =>'$combined' ),
            array( '$sort' => array( 'combined' => 1 ) ),
            array(
                '$group' => array(
                    '_id' => '$_id',
                    'combined' => array( '$push' => '$combined' ),
                    'user_id' => array( '$first' => '$user_id' ),
                    'friend_id' => array( '$first' => '$friend_id' )
                )
            ),
            array(
                '$group' => array(
                    '_id' => '$combined',
                    'docs' => array(
                        '$push' => array(
                            '_id' => '$_id',
                            'user_id' => '$user_id',
                            'friend_id' => 'friend_id'
                        )
                    )
                )
            ),
            array(
                '$redact' => array(
                    '$cond' => array(
                        'if' => array( '$ne' => array( array( '$size' => '$docs'), 1) ),
                        'then' => '$$PRUNE',
                        'else' => '$$KEEP'
                    )
                )
            )
        )
    );
});

或者，如果您的 MongoDB 版本低于 2.6，并且您缺少像 $map 和 $redact 这样的运算符，那么您仍然可以这样做，但效率不高:

$result = DB::collection("collection")->raw(function($collection) {
    return $collection->aggregate(
        array(
            array(
                '$project' => array(
                    'user_id' => 1,
                    'friend_id' => 1,
                    'type' => array( '$const' => array( 'A', 'B' ) )
                )
            ),
            array( '$unwind' => '$type' ),
            array(
                '$group' => array(
                    '_id' => '$_id',
                    'user_id' => array( '$first' => '$user_id' ),
                    'friend_id' => array( '$first' => '$friend_id' ),
                    'combined' => array( 
                        '$push' => array(
                            '$cond' => array(
                                array( '$eq' => array( '$type', 'A' ) ),
                                '$user_id',
                                '$friend_id'
                            )
                        )
                    )
                )
            )
            array( '$unwind' =>'$combined' ),
            array( '$sort' => array( 'combined' => 1 ) ),
            array(
                '$group' => array(
                    '_id' => '$_id',
                    'combined' => array( '$push' => '$combined' ),
                    'user_id' => array( '$first' => '$user_id' ),
                    'friend_id' => array( '$first' => '$friend_id' )
                )
            ),
            array(
                '$group' => array(
                    '_id' => '$combined',
                    'docs' => array(
                        '$push' => array(
                            '_id' => '$_id',
                            'user_id' => '$user_id',
                            'friend_id' => 'friend_id'
                        )
                    ),
                    'count' => array( '$sum' => 1 )
                )
            ),
            array( '$match' => array( 'count' => 1 ) )
        )
    );
});

前三个阶段通过将两个值放在一个数组中来模仿第一阶段在第一个示例列表中所做的事情。当然，最后两个阶段是在分组时“计算”数组成员，然后过滤掉“计数”不为 1 的任何内容。

在任何一种情况下，这都会给您留下仅列出该组合未以任何顺序出现的文档的输出:

{ 
    "_id" : [ "561f40bac2d34f17148b462c", "5625c95ac2d34f27148b64fa" ], 
    "docs" : [ 
        { 
            "_id" : ObjectId("56306f6cd2387ad4c95b0cc9"),
            "user_id" : "5625c95ac2d34f27148b64fa",
            "friend_id" : "561f40bac2d34f17148b462c" 
        } 
    ] 
}
{ 
    "_id" : [ "561f3e06c2d34f27148b45f6", "561f40bac2d34f17148b462c" ], 
    "docs" : [ 
        { 
            "_id" : ObjectId("56306f6cd2387ad4c95b0ccc"), 
            "user_id" : "561f40bac2d34f17148b462c",
            "friend_id" : "561f3e06c2d34f27148b45f6"
        }
    ]
}
{ 
    "_id" : [ "561f40bac2d34f17148b462c", "56248eb9c2d34f2f148b5a18" ], 
    "docs" : [
        { 
            "_id" : ObjectId("56306f6cd2387ad4c95b0ccb"), 
            "user_id" : "56248eb9c2d34f2f148b5a18", 
            "friend_id" : "561f40bac2d34f17148b462c"
        }
    ]
}
{ 
    "_id" : [ "561f40bac2d34f17148b462c", "5620de97c2d34f2f148b578f" ],
    "docs" : [
        { 
            "_id" : ObjectId("56306f6cd2387ad4c95b0ccd"), 
            "user_id" : "561f40bac2d34f17148b462c", 
            "friend_id" : "5620de97c2d34f2f148b578f"
        }
    ]
}
{ 
    "_id" : [ "561f40bac2d34f17148b462c", "562744ccc2d34f27148b6eb7" ], 
    "docs" : [ 
        { 
            "_id" : ObjectId("56306f6cd2387ad4c95b0cca"), 
            "user_id" : "562744ccc2d34f27148b6eb7", 
            "friend_id" : "561f40bac2d34f17148b462c"
        }
    ]
}

您可以美化输出，但这样做的目的是显示与原始文档数据一起使用的有序组合。