Sort documents based on first character in field value

Question

I have a set of data like this:

[{name: "ROBERT"}, {name: "PETER"}, {name: "ROBINSON"} , {name: "ABIGAIL"}]

I want to make a single mongodb query that can find:

Any data which name starts with letter "R" (regex: ^R)
Followed by any data which name contains letter "R" NOT AS THE FIRST CHARACTER, like: peteR, adleR, or caRl

so it produces:

[{name: "ROBERT"}, {name: "ROBINSON"}, {name: "PETER"}]

it basically just display any data that contains "R" character in it but I want to sort it so that data with "R" as the first character appears before the rest

So far I've come out with 2 separate query then followed by an operation to eliminate any duplicated results, then joined them. So is there any more efficient way to do this in mongo ?

Criteria (2) is unclear. It seems like you want the document where `name` contains `R`. If that is the case then [How do I query mongodb with “like”?](https://stackoverflow.com/questions/3305561/how-do-i-query-mongodb-with-like) — styvane, Jun 21 '16 at 04:42
no, I just edited my question to give more clarity. I want to : display all the data where `name` contains `R` but I want to sort it so data with "R" as the first character appears before the rest — DennyHiu, Jun 21 '16 at 05:13

score 2 · Accepted Answer · answered Jun 21 '16 at 10:45

What you want is add a weight to you documents and sort them accordingly.

First you need to select only those documents that $match your criteria using regular expressions.

To do that, you need to $project your documents and add the "weight" based on the value of the first character of your string using a logical $condition processing.

The condition here is $eq which add weight 1 to the document if the lowercase of the first character in the name is "r" or 0 if it's not.

Of course the $substr and the $toLower string aggregation operators respectively return the the first character in lowercase.

Finally you $sort your documents by weight in descending order.

db.coll.aggregate(
    [
        { "$match": { "name": /R/i  } }, 
        { "$project": { 
            "_id": 0, 
            "name": 1, 
            "w": { 
                "$cond": [ 
                    { "$eq": [ 
                        { "$substr": [ { "$toLower": "$name" }, 0, 1 ] }, 
                        "r" 
                    ]}, 
                    1, 
                    0
                ]
            }
        }}, 
        { "$sort": { "w": -1 } } 
    ] 
)

which produces:

{ "name" : "ROBERT", "w" : 1 }
{ "name" : "ROBINSON", "w" : 1 }
{ "name" : "PETER", "w" : 0 }

it's unexpected. Thank you. On the side note though, what should I do if I want to replace my sorting basis from single character to regular expression ? My project demands document sorting based on the first "word/query" that user input — DennyHiu, Jun 22 '16 at 05:09

score -1 · Answer 2 · answered Jun 21 '16 at 04:32

-1

try this :

db.collectioname.find ("name":/R/)

answered Jun 21 '16 at 04:32

Abdul Rehman Sayed

6,532
7
45
74

what it is trying to accomplish ? yes sure it'll display all the data that contains "R" in it but i want to sort it so that "data with R as the first character appears before the data with R as the middle character" – DennyHiu Jun 21 '16 at 05:07

Sort documents based on first character in field value

2 Answers2