蒙戈德布查找查询

Question

seasons.json

{
  "_id" : "unique_1",
  "spring" : [{
      "fruit" : "mango",
      "person_id" : [101.0, 102.0, 103.0, 104.0]
    }, {
      "fruit" : "banana",
      "person_id" : [151.0, 152.0, 153.0, 154.0]
    }],
  "summer" : [{
      "fruit" : "mango",
      "person_id" : [201.0, 202.0, 203.0, 204.0]
    }, {
      "fruit" : "banana",
      "person_id" : [251.0, 252.0, 253.0, 254.0]
    }],
  "fall" : [{
      "fruit" : "mango",
      "person_id" : [301.0, 302.0, 303.0, 304.0]
    }, {
      "fruit" : "banana",
      "person_id" : [351.0, 352.0, 353.0, 354.0]
    }],
  "winter" : [{
      "fruit" : "mango",
      "person_id" : [401.0, 402.0, 403.0]
    }, {
      "fruit" : "banana",
      "person_id" : [451.0, 452.0, 453.0]
    }]
}
/* 2 */
{
  "_id" : "unique_2",
  "spring" : [{
      "fruit" : "banana",
      "person_id" : [151.0, 152.0, 153.0, 154.0]
    }],
  "summer" : [{
      "fruit" : "mango",
      "person_id" : [201.0, 202.0, 203.0, 204.0]
    }, {
      "fruit" : "banana",
      "person_id" : [251.0, 252.0, 253.0, 254.0]
    }],
  "fall" : [{
      "fruit" : "banana",
      "person_id" : [351.0, 352.0, 353.0, 354.0]
    }],
  "winter" : [{
      "fruit" : "mango",
      "person_id" : [401.0, 402.0, 403.0]
    }, {
      "fruit" : "banana",
      "person_id" : [451.0, 452.0, 453.0]
    }]
}

上面的JSON记录显示了哪个季节哪个人吃过芒果，哪个人吃过香蕉。

以下是我想找到的：当我提前或查找记录之前知道记录的_id（主键）时 -

1） 所有person_id范围从 101 - 350 其中person_id是独一无二的2）person_id只吃芒果3）记录中吃水果芒果或香蕉的总人数。

Answer 1

有了这样的模式，运行像你需要的那种性质的查询将非常困难。考虑更改每个子文档的架构，例如一个主键，例如seasons可以有四个不同的数组元素，即春季，夏季，冬季和秋季。将架构更改为：

/* 1 */
{
    "_id" : "unique_1",
    "seasons" : [ 
        {
            "name" : "spring",
            "fruits" : [ 
                {
                    "name" : "mango",
                    "person_id" : [ 
                        101, 
                        102, 
                        103, 
                        104
                    ]
                }, 
                {
                    "name" : "banana",
                    "person_id" : [ 
                        151, 
                        152, 
                        153, 
                        154
                    ]
                }
            ]
        }, 
        {
            "name" : "summer",
            "fruits" : [ 
                {
                    "name" : "mango",
                    "person_id" : [ 
                        201, 
                        202, 
                        203, 
                        204
                    ]
                }, 
                {
                    "name" : "banana",
                    "person_id" : [ 
                        251, 
                        252, 
                        253, 
                        254
                    ]
                }
            ]
        }, 
        {
            "name" : "fall",
            "fruits" : [ 
                {
                    "name" : "mango",
                    "person_id" : [ 
                        301, 
                        302, 
                        303, 
                        304
                    ]
                }, 
                {
                    "name" : "banana",
                    "person_id" : [ 
                        351, 
                        352, 
                        353, 
                        354
                    ]
                }
            ]
        }, 
        {
            "name" : "winter",
            "fruits" : [ 
                {
                    "name" : "mango",
                    "person_id" : [ 
                        401, 
                        402, 
                        403
                    ]
                }, 
                {
                    "name" : "banana",
                    "person_id" : [ 
                        451, 
                        452, 
                        453
                    ]
                }
            ]
        }
    ]
}

使用此架构，运行以下聚合查询变得更加容易：

1） 所有person_id范围从 101 - 350 其中person_id是独一无二的

var pipeline1 = [
    { "$match": { "_id": "unique_1" },
    { "$unwind": "$seasons" },
    { "$unwind": "$seasons.fruits" },
    { "$unwind": "$seasons.fruits.person_id" },
    {
        "$match": {
            "seasons.fruits.person_id": {
                "$gte": 101,
                "$lte": 350
            }
        }
    },    
    {
        "$group": {
            "_id": 0,
            "person_ids": {
                "$addToSet": "$seasons.fruits.person_id"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "person_ids": 1
        }
    }
];
db.season.aggregate(pipeline1);

输出：

/* 1 */
{
    "result" : [ 
        {
            "person_ids" : [ 
                304, 
                253, 
                201, 
                251, 
                301, 
                203, 
                252, 
                204, 
                152, 
                102, 
                202, 
                154, 
                254, 
                101, 
                302, 
                153, 
                104, 
                103, 
                303, 
                151
            ]
        }
    ],
    "ok" : 1
}

2）person_id只吃芒果

var pipeline2 = [
    { "$match": { "_id": "unique_1" },
    { "$unwind": "$seasons" },
    { "$unwind": "$seasons.fruits" },
    { "$unwind": "$seasons.fruits.person_id" },
    {
        "$match": {
            "seasons.fruits.name": "mango"
        }
    },    
    {
        "$group": {
            "_id": 0,
            "person_ids": {
                "$addToSet": "$seasons.fruits.person_id"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "person_ids": 1
        }
    }
];
db.season.aggregate(pipeline2);

输出：

/* 1 */
{
    "result" : [ 
        {
            "person_ids" : [ 
                402.0000000000000000, 
                304.0000000000000000, 
                303.0000000000000000, 
                302.0000000000000000, 
                301.0000000000000000, 
                204.0000000000000000, 
                202.0000000000000000, 
                201.0000000000000000, 
                203.0000000000000000, 
                104.0000000000000000, 
                102.0000000000000000, 
                103.0000000000000000, 
                403.0000000000000000, 
                401.0000000000000000, 
                101.0000000000000000
            ]
        }
    ],
    "ok" : 1
}

3）记录中吃水果芒果或香蕉的总人数。

var pipeline3 = [
    { "$match": { "_id": "unique_1" },
    { "$unwind": "$seasons" },
    { "$unwind": "$seasons.fruits" },
    { "$unwind": "$seasons.fruits.person_id" },
    {
        "$match": {
            "seasons.fruits.name": {
                "$in": ["mango", "banana"]
            }
        }
    },    
    {
        "$group": {
            "_id": "$_id",
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "count": 1
        }
    }
];
db.season.aggregate(pipeline3);

输出：

/* 1 */
{
    "result" : [ 
        {
            "count" : 30
        }
    ],
    "ok" : 1
}