matching two string object fields in mongo aggregation

Question

> db.doc.find().pretty();
{
    "_id" : ObjectId("55669401a5f628a23fed5adc"),
    "cur" : {
        "pathname" : "/blog",
        "href" : "http://www.example.com/blog/"
    },
    "visits" : [
        {
            "url" : "http://www.example.com/blog/",
            "gt_ms" : "1110"
        }
    ]
}
{
    "_id" : ObjectId("556697eba5f628a23fed5add"),
    "cur" : {
        "pathname" : "/twcontroller/insights/login.php",
        "href" : "http://www.example.com/twcontroller/insights/login.php"
    },
    "visits" : [
        {
            "url" : "http://www.example.com/twcontroller/insights/login.php",
            "gt_ms" : "990"
        }
    ]
}
{
    "_id" : ObjectId("556697eba5f628a23fed5ade"),
    "cur" : {
        "pathname" : "/",
        "href" : "http://www.example.com/"
    },
    "visits" : [
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "719"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "719"
        }
    ]
}
{
    "_id" : ObjectId("556697eba5f628a23fed5adf"),
    "cur" : {
        "pathname" : "/",
        "href" : "http://www.example.com/"
    },
    "visits" : [
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        },
        {
            "url" : "http://www.example.com/",
            "gt_ms" : "62"
        }
    ]
}

I want to match the value of cur.pathname with the visits[0].url (i.e., the first item in the array visits) and returns its count. Also want to return the count of visits array which is having only one array element.

How can I do this?

Looking at your sample documents if you compare `cur.pathname` and `visits[0].url` it will always yield `False`. Did you mean `cur.href` and `visits[0].url` ? — thegreenogre, May 28 '15 at 12:03
I need the first url value in visits array, means value of url at first index. like visits[0].url in JS — Afeela Ashraf, May 28 '15 at 12:11
You can use the [`$slice`](http://docs.mongodb.org/manual/reference/operator/projection/slice/#slice-projection) operator in projection or have a look at this [answer](http://stackoverflow.com/questions/26762887/get-first-element-in-array-and-return-using-aggregate-mongodb) to do it in aggregation. — thegreenogre, May 28 '15 at 12:27

score 4 · Accepted Answer · answered May 28 '15 at 13:02

Currently Array Position Operator is not available for aggregation. There is an unresolved open issue linked to it: https://jira.mongodb.org/browse/SERVER-4588 and https://jira.mongodb.org/browse/SERVER-4589 .

You will have to use the $first operator to project the first element of array. This following query should give you the result you want. (I am assuming you want to compare cur.href and visits[0].url.)

db.Testing.aggregate([
    {
        "$project": {
            "hasOneElement": {
                "$cond": [
                    { "$eq": [{"$size": "$visits"}, 1] },
                    1,
                    0
                ]
            },
            "href": "$cur.href",
            "visits.url" : 1
        }
    },
    {'$unwind': '$visits'},
    {'$group': {
        '_id': "$_id",
        'href': {'$first': '$href'}, 
        'visits': {'$first': '$visits'},
        'hasOneElement': {'$first': '$hasOneElement'} 
        }
    },
    {
        '$project': {
            'hasOneElement': 1,
            "isMatch": {
                "$cond": [
                    { "$eq": ["$href", "$visits.url"] },
                    1,
                    0
                ]
            }
        }
    },
    {
        '$group': {
            '_id' : 'test',
            'visit_count' : {'$sum' : '$isMatch' },
            'oneelement_count' : {'$sum' : "$hasOneElement" }
        }
    }
])

Result for Sample Document Supplied.

{
    "result" : [ 
        {
            "_id" : "test",
            "visit_count" : 4,
            "oneelement_count" : 2
        }
    ],
    "ok" : 1
}

matching two string object fields in mongo aggregation

1 Answers1