sort Mongodb find() output according to regex

Question

db.restaurant_info.find({name:/pi/i})

Above mongodb query is returning the data from DB in the below format

{
    "_id" : ObjectId("579cf26204aba69a41da82ad"),
    "name" : "pizza hut",
    "type" : "restaurant"
}

/* 2 */
{
    "_id" : ObjectId("579cf26204aba69a41da82af"),
    "name" : "Kai pi",
    "type" : "restaurant"
}

/* 3 */
{
    "_id" : ObjectId("579cf26404aba69a41da82c7"),
    "name" : "pizza and pasta",
    "type" : "restaurant"
}

/* 4 */
{
    "_id" : ObjectId("579cf26504aba69a41da82d0"),
    "name" : "Crispi chicken",
    "type" : "restaurant"
}

/* 5 */
{
    "_id" : ObjectId("579cf26504aba69a41da82d1"),
    "name" : "Pita house",
    "type" : "restaurant"
}

However, this query will be used for auto-population so if I use pi in the search text field, then all the recodrs start with pi should come before other records, e.g sequence I am expecting:

Pizza hut
Pizza and pasta
Pita house
Kai pi
Crispi chicken

if I modify the query with db.restaurant_info.find({name:/^pi/i}), then it returns

Pizza hut
Pizza and pasta
Pita house

without

Kai pi
Crispi Chicken

Please guide me which query should I use to get the sequence I am expecting.

Wake · Accepted Answer · 2016-08-02 00:55:00Z

2

I don't know how you would do this with find(), but you could do it with aggregate().

First you'd use your regex for the $match, then you could use $project with $substring and $eq to project a field indicating whether you have a prefix match. You could then use that field to sort your results. Here is an example. I also used $toLower so that your sorting would be case insensitive.

{
    $match: {
        name:/pi/i
    }
}, 
{
    $project: {
        name:true,
        regex_match: {
            $eq: [
                "pi", 
                { $substr: [ {$toLower: "$name"}, 0, 2 ] }
            ]
        }

    }
}, 
{
    $sort: {regex_match:-1}
}

answered Aug 2, 2016 at 0:55

Wake

1,69610 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sort Mongodb find() output according to regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related