MongoDB高级查询(二):对一个表中的2个字段中的"内嵌对象"中的字段进行比较----------- aggregate聚合查询

上一篇文章我们说到用 $where,但是这个办法有个严重的弊端: 性能消耗太多,一般是禁止这样进行使用script的,查询起来很慢.

闲言少叙 书归正传!

1.需要查询的表格

{
	"health" : {
			"diastolicpressure" : 95,
			"systolicpressure" : 155,
			"waist" : 72,
	},
	"calculation" : {
			"target" : {
				"systolicpressure" : 138,
				"diastolicpressure" : 80,
				"weight" : 67.375
			},
	
	}
}

{
	"health" : {
			"diastolicpressure" : 79,
			"systolicpressure" : 120,
			"waist" : 50,
	},
	"calculation" : {
			"target" : {
				"systolicpressure" : 138,
				"diastolicpressure" : 80,
				"weight" : 67.375
			},
	
	}
}

2.需要查询的需求:

找出表格中health.diastolicpressure > calculation.target.diastolicpressure的数据

3单个条件比较查询实现 在shell中

db.members.aggregate(
    [{
            $match: {
                sid: "aaa-PJx"   //筛选--先匹配这个,满足这个的才进行下面的匹配
            }
        },
        {
            $redact: {
                "$cond": [{
                        "$gt": ["$health.systolicpressure", "$calculation.target.systolicpressure"]
                    },
                    , "$$KEEP", "$$PRUNE"
                ]
            }
        }
    ]
).pretty()

4.多个条件比较查询实现 在shell中

db.members.aggregate(
    [
        {
            $match: {
                sid: "aaa-PJx",
            }
        },
        {
            $redact: {
                "$cond": [
                    {
                        "$and": [
                            {
                                "$gt": ["$health.systolicpressure", "$calculation.target.systolicpressure"]                        
                            },
                            {
                                "$gt": ["$health.diastolicpressure", "$calculation.target.diastolicpressure"]
                            }
                        ]
                    },
                    "$$KEEP", "$$PRUNE"
                ]
            }
        }
    ]
).pretty()