MongoDB中找到查询

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 
}