Mongodb find query

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]
    }]
}

Above JSON records shows which season which person has eaten mango and which has eaten banana.

Here's what I want to find: when i know the _id(primary key) of the record in advance or prior to record finding -

1) all the person_id ranging from 101 - 350 in which person_id is unique 2) person_id eating only mango 3) total number of person in a record eating fruit either mango or banana.

Answer 1

With a schema like this it's going to be pretty difficult to run queries of such a nature like the ones you require. Consider changing the schema such that you have for each subdocument, one main key say for instance seasons which can have four different array elements i.e. spring, summer, winter and fall. Change the schema to this:

/* 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
                    ]
                }
            ]
        }
    ]
}

With this schema it becomes much easier to run the following aggregation queries:

1) all the person_id ranging from 101 - 350 in which person_id is unique

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);

Output:

/* 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 eating only mango

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);

Output:

/* 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) total number of person in a record eating fruit either mango or banana.

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);

Output:

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