mongodbrockmongo

How to compare and count each value of element with condition in mongoDB pipeline after unwinding?


This is my command I ran in tools->command

{
aggregate : "hashtags",       
pipeline:

[

{$unwind:"$time"},
{$match:{"$time":{$gte:NumberInt(1450854385), $lte:NumberInt(1450854385)}}},
{$group:{"_id":"$word","count":{$sum:1}}}
]

}

which gave us this result

Response from server:
{
   "result": [
     {
       "_id": "dear",
       "count": NumberInt(1) 
    },
     {
       "_id": "ghost",
       "count": NumberInt(1) 
    },
     {
       "_id": "rat",
       "count": NumberInt(1) 
    },
     {
       "_id": "police",
       "count": NumberInt(1) 
    },
     {
       "_id": "bugs",
       "count": NumberInt(3) 
    },
     {
       "_id": "dog",
       "count": NumberInt(2) 
    },
     {
       "_id": "batman",
       "count": NumberInt(9) 
    },
     {
       "_id": "ear",
       "count": NumberInt(1) 
    } 
  ],
   "ok": 1 
}

The documents are in collection 'hashtags' The documents inserted are as shown below 1.

{
   "_id": ObjectId("567a483bf0058ed6755ab3de"),
   "hash_count": NumberInt(1),
   "msgids": [
     "1583" 
  ],
   "time": [
     NumberInt(1450854385) 
  ],
   "word": "ghost" 
}

2.

{
   "_id": ObjectId("5679485ff0058ed6755ab3dd"),
   "hash_count": NumberInt(1),
   "msgids": [
     "1563" 
  ],
   "time": [
     NumberInt(1450788886) 
  ],
   "word": "dear" 
}

3.

{
   "_id": ObjectId("567941aaf0058ed6755ab3dc"),
   "hash_count": NumberInt(9),
   "msgids": [
     "1555",
     "1556",
     "1557",
     "1558",
     "1559",
     "1561",
     "1562",
     "1584",
     "1585" 
  ],
   "time": [
     NumberInt(1450787170),
     NumberInt(1450787292),
     NumberInt(1450787307),
     NumberInt(1450787333),
     NumberInt(1450787354),
     NumberInt(1450787526),
     NumberInt(1450787615),
     NumberInt(1450855148),
     NumberInt(1450855155) 
  ],
   "word": "batman" 
}

4.

{
   "_id": ObjectId("567939cdf0058ed6755ab3d9"),
   "hash_count": NumberInt(3),
   "msgids": [
     "1551",
     "1552",
     "1586" 
  ],
   "time": [
     NumberInt(1450785157),
     NumberInt(1450785194),
     NumberInt(1450856188) 
  ],
   "word": "bugs" 
}

So I want to count the number of values in the field 'time' which comes in between two limits

such as this

foreach word
{
foreach time
{
if((a<time)&&(time<b))
word[count]++
}
}

but my query is just giving output of the total size of array 'time'.

What is the correct query?

for eg

if lower bound is 1450787615  and upper bound is  1450855155

there are 3 values in 'time'. for word 'batman'

The answer should be
{
           "_id": "batman",
           "count": NumberInt(3) 
        },

for batman.Thank you.


Solution

  • Use the following aggregation pipeline:

    db.hashtags.aggregate([
        {
            "$match": {
                "time": {
                    "$gte": 1450787615, "$lte": 1450855155  
                }
            }
        },
        { "$unwind": "$time" },
        {
            "$match": {
                "time": {
                    "$gte": 1450787615, "$lte": 1450855155  
                }
            }
        },
        {
            "$group": {
                "_id": "$word",
                "count": {
                    "$sum": 1
                }
            }
        }
    ])
    

    For the given sample documents, this will yield:

    /* 0 */
    {
        "result" : [ 
            {
                "_id" : "batman",
                "count" : 3
            }, 
            {
                "_id" : "dear",
                "count" : 1
            }, 
            {
                "_id" : "ghost",
                "count" : 1
            }
        ],
        "ok" : 1
    }