Aggregation framework : select substring of a field

[MANU]

Hi guys,

what the correct syntax for the operator $substr ?

I tried :

db.toto.save(  { author : "Simon" , title : "toto"   } )

db.toto.aggregate(
    { $match : { author : "Simon" , $substr(title,1,1) : "t"  } }
);

and I get this error :
Fri Jun  1 16:05:28 SyntaxError: missing : after property id (shell):1

thanks by advance

Manu

[craiggwilson]

try this, the field name goes first and the values to the function are passed as an array.

db.toto.aggregate(
    { $match : { author : "Simon" , title: { $substr :["t",1,1] } } }
); 

[MANU]

it doesn't work :

> db.toto.find()
{ "_id" : ObjectId("4fc8cbdd3c2c5c4ccf30d582"), "author" : "Simon", "title" : "toto" }

> db.toto.aggregate(     { $match : { author : "Simon" , title: { $substr :["t",1,1] } } } );

{
    "errmsg" : "exception: invalid operator: $substr",
    "code" : 10068,
    "ok" : 0
}

my goal is to get a group by with the first letter of a field,  example :

> db.toto.find()
{ "author" : "Simon", "title" : "toto" , nb :5 }
{ "author" : "Simon", "title" : "toto" , nb :2 }
{ "author" : "Simon", "title" : "azerty" , nb :12 }
{ "author" : "Simon", "title" : "azerty" , nb :5 }

I want to have :

Simon, t , 7
Simon , a , 17

what I tried :

db.toto.aggregate(
    { $group : {
        _id : {author : "$author",titre : { $substr :["$title",0,1] } },
        sum : { $sum : "$nb" }
    }}
);

And that works really fine,

thank you :)

[MANU]

I want to do this SQL equivalent in mongo :

select substr(title,0,1) from article

> db.article.find()
{ "_id" : ObjectId("4fc8cbdd3c2c5c4ccf30d582"), "author" : "Simon", "title" : "toto" }

I want to find every article where the first letter of title is "t"

db.article.find( { title : { $substr :["t",1,1]  }  }  )
error: { "$err" : "invalid operator: $substr", "code" : 10068 }

I don't want to use a regexp beacause we don't have index on the field "title"

Thank's a lot by advance

[craiggwilson]

you can use the $project operator of the aggregate method for this.

db.article.aggregate({ 

  $project: { 

    title: { $substr: ["$title", 0, 1] } 

  }

});

You cannot, however, use the $substr expression in a query or $match pipeline operator.

[craiggwilson]

BTW: If you are going to be doing this type of querying a lot, I'd suggest you go ahead and denormalize your schema and include the first letter of each title in the stored document.  You could index that field and filter on it.