Mongodb常用查询

葛大爷互联网 2017-10-17 3662

原始数据

{
    "_id" : ObjectId("59e56eca879af8e552ef7e3e"),
    "supplierOrderCode" : "111111111197",
    "batchNo" : "HWA02Z1017001",
    "schedule" : "2017-10-17",
    "batchStatus" : "过账完成",
    "productLine" : "FE",
    "materiel" : [ 
        {
            "code" : "02350HWW-F50",
            "remark" : "测试",
            "num" : 10.0
        }, 
        {
            "code" : "88094401-FA",
            "remark" : "测试",
            "num" : 10.0
        }, 
        {
            "code" : "02350HWW-F50",
            "remark" : "测试2",
            "num" : 5.0
        }
    ]
}
...

获取数组匹配的第一条

db.getCollection('table').find({'supplierOrderCode': {$regex: '111111111197', $options: 'i'}, 'batchNo' : 'HWA02Z1017001', 'materiel.code': '02350HWW-F50'},{ 'materiel.$': 1 })

结果：

{
    "_id" : ObjectId("59e56eca879af8e552ef7e3e"),
    "materiel" : [ 
        {
            "code" : "02350HWW-F50",
            "remark" : "测试",
            "num" : 10.0
        }
    ]
}

拆分数组并分页

db.getCollection('table').aggregate({$unwind: '$materiel'}, 
    {$project: {supplierOrderCode: 1, productLine: 1, batchNo: 1, schedule: 1, batchStatus: 1, sremark: 1, materiel: 1}},
    {$match: {'supplierOrderCode': {$regex: '111111111197', $options: 'i'}, 'batchNo' : 'HWA02Z1017001', 'materiel.code': '02350HWW-F50'}},
    {$skip: 0},
    {$limit: 10})

结果:

/* 1 */
{
    "_id" : ObjectId("59e56eca879af8e552ef7e3e"),
    "supplierOrderCode" : "111111111197",
    "batchNo" : "HWA02Z1017001",
    "schedule" : "2017-10-17",
    "batchStatus" : "过账完成",
    "productLine" : "FE",
    "materiel" : {
        "code" : "02350HWW-F50",
        "remark" : "测试",
        "num" : 10.0
    }
}
/* 2 */
{
    "_id" : ObjectId("59e56eca879af8e552ef7e3e"),
    "supplierOrderCode" : "111111111197",
    "batchNo" : "HWA02Z1017001",
    "schedule" : "2017-10-17",
    "batchStatus" : "过账完成",
    "productLine" : "FE",
    "materiel" : {
        "code" : "02350HWW-F50",
        "remark" : "测试2",
        "num" : 5.0
    }
}

获取拆分数组的总条数

db.getCollection('table').aggregate({$unwind: '$materiel'},
    {$match: {'supplierOrderCode': {$regex: '111111111197', $options: 'i'}, 'batchNo' : 'HWA02Z1017001', 'materiel.code': '02350HWW-F50'}},
    {$group : {_id : null, count : {$sum : 1}}})

结果：

{
    "_id" : null,
    "count" : 2.0
}

复杂查询

db.getCollection('table').aggregate({$unwind: '$materiel'}, 
    {$match: {'supplierOrderCode': {$regex: '111111111197', $options: 'i'}, 'batchNo' : 'HWA02Z1017001'}},
    {$group: {
        _id: '$supplierOrderCode',
        isEnd: {$sum: {$cond: {if: {$eq: ['$batchStatus', '过账完成']}, then: 1, else: 0}}},
        minSchedule: {$min: '$schedule'},
        maxSchedule: {$max: '$schedule'},
        materielCode: {$addToSet: '$materiel.code'}
,        materielCode2: {$push: '$materiel.code'}
      }}
)

结果：

{
    "_id" : "111111111197",
    "noEnd" : 3.0,
    "minSchedule" : "2017-10-17",
    "maxSchedule" : "2017-10-17",
    "materielCode" : [ 
        "88094401-FA", 
        "02350HWW-F50"
    ],
    "materielCode2" : [ 
        "02350HWW-F50", 
        "88094401-FA", 
        "02350HWW-F50"
    ]
}