javascript - mongo二维数据查询出最大值,最小值

Question

数据如下
表名:demo

{
"date":ISODate("2016-07-21")
"name":"userno"
"items":{
        "00:01":{
            "press":"1111",
        },
        "00:02":{
            "press":"1121",
        },
        "00:03":{
            "press":"2111",
        },
        ....
        "23:58":{
            "press":"1311",
        },
        "23:59":{
            "press":"1511",
        }
    }
},

{
"date":ISODate("2016-07-20")
"name":"userno"
"items":{
        "00:01":{
            "press":"4111",
        },
        "00:02":{
            "press":"1621",
        },
        "00:03":{
            "press":"2177",
        },
        ....
        "23:58":{
            "press":"1311",
        },
        "23:59":{
            "press":"1519",
        }
    }
},

{
"date":ISODate("2016-07-20")
"name":"userno222222"
"items":{
        "00:01":{
            "press":"4111",
        },
        "00:02":{
            "press":"1621",
        },
        "00:03":{
            "press":"2177",
        },
        ....
        "23:58":{
            "press":"1311",
        },
        "23:59":{
            "press":"1519",
        }
    }
},

现在要求name为userno,date是2016-07-21的数据中的press的最大值和最小值
请问mongo的sql应该怎么写

Answer 1

你的文档结构没有办法支持aggregation的$group/$max操作。另外items下面的子文档也不好建索引。
建议这样的结构：

{
    "date": ISODate("2016-07-21"),
    "name": "userno",
    "items": [{
        "time": "00:01",
        "press": "1111",
    }, {
        "time": "00:02",
        "press": "1121",
    }, {
        "time": "00:03",
        "press": "2111",
    }, {
        "time": "23:58",
        "press": "1311",
    }, {
        "time": "23:59",
        "press": "1511",
    }]
}

这样修改之后可以使用aggregation方便地取得最小和最大press：

db.demo.aggregate([
    {$match: {date: ISODate("2016-07-21"), name: "userno"}},
    {$unwind: "$items"},
    {$group: {_id: null, min: {$min: "$items.press"}, max: {$max: "$items.press"}}}
])

这建立在对aggreation framework的正确理解上。相关知识请查阅文档。

Answer 2

1.查询文档，之后对文档做极值

var doc = db.test.find({userno:"userno",date:"2016-07-21"})
getMaxAndMin = function(doc){
    items = doc.items;
    var max = 0;
    var min = 10000;
    for (var key in items) {
        var value = items[key]['press'];
        if (value > max) {
            max = value;
        }
        if (value < min) {
            min = value;
        }
    }
    return {"max": max, "min": min};
 }
        
doc.apply(getMaxAndMin )
    

2.使用group

db.test.group({key:{"name":true},condition:{"name":"userno"},initial:{max:0,min:10000},$reduce:reduce})

reduce =  function (doc,prev){
    var items = doc.items;
    var max = prev.max;
    var min = prev.min;
    for(var key in items){
        var value = items[key]['press'];
        if(value > max){
            max = value;
        }
        if(value < min){
            min = value;
        }
    }
    prev.max = max;
    prev.min = min;
}