Mongodb常用查询

葛大爷 互联网 2017-10-17 894
  1. 原始数据
    {
    "_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
    }
    ]
    }
    ...
  2. 获取数组匹配的第一条
    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
    }
    ]
    }
  3. 拆分数组并分页
    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
    }
    }
  4. 获取拆分数组的总条数
    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
    }
  5. 复杂查询
    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"
    ]
    }