关于mongodb的多个嵌套子文档同时group by

Question

现在有一个文档集合 col ，其中文档记录结构如下,集合当中有多个这样的文档记录：

{"_id":1,
 "shares": [
            {
                "fundcode": "000001",     
                "lastshares": 1230.20,
                "agencyno":"260",
                "netno":"260"
            },
            {
                "fundcode": "000002",
                "lastshares": 213124.00,
                "agencyno":"469",
                "netno":"001"
            },
            {
                "fundcode": "000003",
                "lastshares": 10000.80,
                "agencyno":"469",
                "netno":"002"
            }
        ],
    "trade":[
            {
                "fundcode": "000001",
                "c_date":"20160412",
                "agencyno":"260",
                "netno":"260",
                "bk_tradetype":"122",
                "confirmbalance": 1230.20,
                "cserialno":"10110000119601",
                "status":"1"
            },
            {
                "fundcode": "000002",
                "c_date": "20160506",
                "agencyno":"469",
                "netno":"001",
                "bk_tradetype":"122",
                "confirmbalance": 213124.00,
                "cserialno":"10110000119602",
                "status":"1"
            },
            {
                "fundcode": "000003",
                "c_date": "20170507",
                "agencyno":"469",
                "netno":"002",
                "bk_tradetype":"122",
                "confirmbalance": 10000.80,
                "netvalue":1.0000,
                "cserialno":"10110000119602",
                "status":"1"
            }
        ]
}

现在我要找到符合特定条件的记录，实现一个类似以下逻辑的SQL。我该怎么写mongodb的查询语句呢？

select _id
from col
where col.shares.lastshares>1000 and col.trade.agencyno='469'
group by _id
having count(distinct col.shares.fundcode)>2 and count(distinct col.trade.fundcode)>2

用一次$unwind还行，但连续使用$unwind（先shares,再confirm),再$match ,再$group,再$match的逻辑不太正确，还请牛人指点，谢谢~

Answer 1

插入语句

db.getCollection('test1').insert(
{
"shares": [
        {
            "fundcode": 000001,     
            "lastshares": 1230.20,
            "agencyno":"260",
            "netno":"260"
        },
        {
            "fundcode": 000002,
            "lastshares": 213124.00,
            "agencyno":"469",
            "netno":"001"
        },
        {
            "fundcode": 000003,
            "lastshares": 10000.80,
            "agencyno":"469",
            "netno":"002"
        }
    ],
"trade":[
        {
            "fundcode": 000001,
            "c_date":"20160412",
            "agencyno":"260",
            "netno":"260",
            "bk_tradetype":"122",
            "confirmbalance": 1230.20,
            "cserialno":"10110000119601",
            "status":"1"
        },
        {
            "fundcode": 000002,
            "c_date": "20160506",
            "agencyno":"469",
            "netno":"001",
            "bk_tradetype":"122",
            "confirmbalance": 213124.00,
            "cserialno":"10110000119602",
            "status":"1"
        },
        {
            "fundcode": 000003,
            "c_date": "20170507",
            "agencyno":"469",
            "netno":"002",
            "bk_tradetype":"122",
            "confirmbalance": 10000.80,
            "netvalue":1.0000,
            "cserialno":"10110000119602",
            "status":"1"
        }
    ]
}
)

查询SQL

db.getCollection('test1').aggregate([
{
    $match: {
        'trade.agencyno': {$eq:'469'},
        'shares.lastshares': {$gt: 1000},
                'shares.fundcode' : {$gt: 2},
                'trade.fundcode' : {$gt: 2},
    }
},{
    $group: {
        _id: { 'id':'$_id',
                'shares': '$shares.fundcode',
                'trade': '$trade.fundcode'
            },
    }
},{
    $project: {
        '_id': '$_id._id',
        'shares': {$size: '$_id.shares'},
        'trade': {$size: '$_id.trade'}
    }
},{
    $match: {
        'shares': {$gt: 2},
        'trade': {$gt: 2}
    }
}
]);

这样写可以,不过有个问题,这样写,shares.fundcode和trade.fundcode必须为数字类型的,字符串类型的查询不到记录.
并且感觉这SQL还有优化的空间

Answer 2

先定義重複的資料條件
然後用 map reduce 統計 > 1 的就是有重複的資料
請參考：
http://forum.foxera.com/mongo...