'like' or $regex query inside $cond in MongoDB

Question

Please go through this question of mine:
MongoDB $group and explicit group formation with computed column

But this time, I need to compare strings, not numbers.The CASE query must have a LIKE:
CASE WHEN source LIKE '%Web%' THEN 'Web'

I then need to group by source. How to write this in Mongo? I am trying the following but not sure if $regex is supported inside $cond. By the way, is there a list of valid operators inside $cond somewhere? Looks like $cond isn't very fond of me :)

db.Twitter.aggregate(
    { $project: { 
        "_id":0,
        "Source": {
            $cond: [
                { $regex:['$source','/.* Android.*/'] },
                'Android', 
                { $cond: [
                    { $eq: ['$source', 'web'] }, 'Web', 'Others'
                ] } 
            ]
        }
    } }
);

There're many other values that I need to write in there, doing a deeper nesting. This is just an example with just 'Android' and 'Web' for the sake of brevity. I have tried both with $eq and $regex. Using $regex gives error of invalid operator whereas using $eq doesn't understand the regex expression and puts everything under 'Others'. If this is possible with regex, kindly let me know how to write it for case-insensitive match.

Thanks for any help :-)

You can only use [aggregation operators](http://docs.mongodb.org/manual/reference/aggregation/#boolean-operators) that evaluate to a boolean for the first parameter of a `$cond`. So no `$regex`. — JohnnyHK, Jan 21 '13 at 16:44
Ohh :( I have successfully used $eq inside $cond in one of my queries..But this is a 'LIKE' query..Isn't there a workaround? Maybe I need to clean my data separately before aggregating.. — Aafreen Sheikh, Jan 22 '13 at 05:43

score 1 · Answer 1 · answered Mar 04 '15 at 14:31

1

Well, it still seems to be not even scheduled to be implemented :( https://jira.mongodb.org/browse/SERVER-8892

I'm using 2.6 and took a peek on 3.0, but it's just not there.

There's one workaround though, if you can project your problem onto a stable substring. Then you can $substr the field and use multiple nested $cond. It's awkward, but it works.

answered Mar 04 '15 at 14:31

pkopac

986
1
13
21

Could you develop a little more your workaround, it's a bit unclear... Thank you ! – Anthony O. Sep 10 '15 at 08:09
First I projected the value with $substr, for example giving me 0-10th characters of the string, then I checked for equality, which is possible in $cond. By "stable" I meant stable position of substring in checked strings. But since then we've rewritten the thing into Spark. – pkopac Sep 11 '15 at 11:16
OK thank you. So it's not really a Regexp, I understand. – Anthony O. Sep 11 '15 at 12:49
1

No, regexp is not possible, it's just a workaround for a very specific case. – pkopac Sep 11 '15 at 13:20

rubenfa · Answer 2 · 2013-11-27T21:58:21.807

Maybe you can try it with MapReduce.

var map = function()
{
    var reg1=new RegExp("(Android)+");
    var reg2=new RegExp("(web)+");

    if (reg1.test(this.source)){
        emit(this._id,'Android');
    }
    else if  (reg2.test(this.source))
    {
        emit(this._id,'web');
    }

}

var reduce = function (key,value){
    var reduced = {
        id:key,
        source:value
    }    
    return reduced;

}

db.Twitter.mapReduce(map,reduce,{out:'map_reduce_result'});

db.map_reduce_result.find();

You can use JavaScript regular expresions instead of MongoDB $regex.

'like' or $regex query inside $cond in MongoDB

2 Answers2

Linked