检查从mongoDB聚合管道返回的相同值，即使记录键/ID不同

Question

我在mongoDB中有一个聚合管道，它返回三个随机记录，但我不希望这些记录的任何值是相同的。例如，我不希望transactionDetail说"foo“的两个实例，即使它们来自不同的记录。我怎样才能做到这一点？

使用mongoDB查询

db.TABLE.aggregate([{$match: {   pid:{$ne:00000021},   transactionDetail:{$ne:null} }}, {$limit: 100}, {$sample: {   size: 3 }}, {$project: {   transactionDetail:1,   _id: 0 }}])

数据的示例如下所示

{ "_id" : ObjectId("5da92bb0b3507b13a8d63556"), "pid" : 00000001, "transactionDate" : "2019-10-10 13:05:00", "transactionValue" : 0.00, "transactionDetail" : "HOME & THINGS-C/SPRING RO", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb0b3507b13a8d63557"), "pid" : 00000002, "transactionDate" : "2019-10-10 06:15:29", "transactionValue" : 0.00, "transactionDetail" : "JUICI PATTIES - LIGUANEA ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb0b3507b13a8d63558"), "pid" : 00000003, "transactionDate" : "2019-10-10 10:52:32", "transactionValue" : 0.00, "transactionDetail" : "MATILDA'S CORNER PHARMACY", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb0b3507b13a8d63559"), "pid" : 00000004, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : null, "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355a"), "pid" : 00000005, "transactionDate" : "2019-10-11 00:00:00", "transactionValue" : 0.00, "transactionDetail" : null, "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355b"), "pid" : 00000006, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : null, "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355c"), "pid" : 00000007, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : null, "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355d"), "pid" : 00000008, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : "COURTS - CONSTANT SP RD  ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355e"), "pid" : 00000009, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : "COURTS - CONSTANT SP RD  ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d6355f"), "pid" : 00000010, "transactionDate" : "2019-10-10 13:14:44", "transactionValue" : 0.00, "transactionDetail" : "CARIBBEAN FLAVORS AND FRA", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63560"), "pid" : 00000011, "transactionDate" : "2019-10-10 17:47:40", "transactionValue" : 0.00, "transactionDetail" : "HUA SQUARE LIMITED       ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63561"), "pid" : 00000012, "transactionDate" : "2019-10-10 16:28:02", "transactionValue" : 0.00, "transactionDetail" : "UNIPET - GREGORY PARK    ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63562"), "pid" : 00000013, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : "DIGICEL DING*    ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63563"), "pid" : 00000014, "transactionDate" : "2019-10-10 12:34:55", "transactionValue" : 0.00, "transactionDetail" : "HI-LO - CROSS ROADS      ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63564"), "pid" : 00000015, "transactionDate" : "2019-10-11 00:00:00", "transactionValue" : 0.00, "transactionDetail" : "FIRE MOUNTAIN GEMS & BEAD", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63565"), "pid" : 00000016, "transactionDate" : "2019-10-10 00:00:00", "transactionValue" : 0.00, "transactionDetail" : "AMZN Mktp US*   ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63566"), "pid" : 00000017, "transactionDate" : "2019-10-11 04:23:08", "transactionValue" : 0.00, "transactionDetail" : "UBER   TRIP              ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63567"), "pid" : 00000018, "transactionDate" : "2019-10-10 18:48:37", "transactionValue" : 0.00, "transactionDetail" : "SHOPPERS FAIR - MANDEVILL", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63568"), "pid" : 00000019, "transactionDate" : "2019-10-10 06:33:29", "transactionValue" : 0.00, "transactionDetail" : "RUBIS WATERLOO - MLT G A ", "system" : "REDACTED" }
{ "_id" : ObjectId("5da92bb2b3507b13a8d63569"), "pid" : 00000020, "transactionDate" : "2019-10-10 21:31:23", "transactionValue" : 0.00, "transactionDetail" : "EASYWORSHIP              ", "system" : "REDACTED" }

如您所见，记录00000008和00000009包含相同的transactionDetail值，但我不希望管道返回该值。

到目前为止，这是我的管道。

[
    {
        '$match': {
            'pid': {
                '$ne': idnum
            }, 
            'transactionDetail': {
                '$ne': None
            }
        }
    }, {
        '$limit': 1000
    }, {
        '$sample': {
            'size': 3
        }
    }, {
        '$project': {
            'transactionDetail': 1, 
            '_id': 0
        }
    }
]

Answer 1

因此，查看示例数据集，并查看所提供的查询，输出所需的唯一字段是“transactionDetail”。我相信你想要3个随机的唯一的/不同的/相互排斥的值。我想这个查询会.

db.TABLE.aggregate(
[
    { '$match': {
            'pid': { '$ne': "00000021" }, 
            'transactionDetail': { '$ne': null }
    }},
    { $group: {
        _id: '$transactionDetail',
        count: { $sum: 1}
    }},
    { '$limit': 1000 }, 
    { '$sample': { 'size': 3 } },
    { '$project': {
            '_id': 1
        }
    }
]
)

策略是使用$group映射-在执行$sample之前将重复的值减少到唯一的值。

这能达到你所追求的目标吗？