Multiple nested subdocuments on mongodb group by at the same time

  mongodb, question

Now there is a document collection col, in which the document record structure is as follows, and there are many such document records in the collection:

{"_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"
 }
 ]
 }

Now I want to find records that meet certain conditions and implement a SQL similar to the following logic. How should I write mongodb’s query statement?

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

One use of $unwind is fine, but the logic of using $unwind continuously (first shares, then confirm), then $match, then $group, and then $match is not quite correct. Please give some advice, thank you ~

Insert statement

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"
 }
 ]
 }
 )

Query 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}
 }
 }
 ]);

It is possible to write this way, but there is a problem. To write this way, shares.fundcode and trade.fundcode must be of numeric type, and the query of string type cannot be recorded.
And feel this SQL still has room for optimization