23

I can do it easily on mysql 

select * from TABLE order by length(FIELD) asc

How can I do it on MongoDB?

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
Henry Liu
  • 671
  • 1
  • 10
  • 19
  • 7
    http://stackoverflow.com/questions/14647644/select-string-length-in-mongodb if you want it to be efficient, I'd store the length of the string and sort with that. – WiredPrairie Mar 18 '13 at 11:00
  • @MagnusTengdahl, I tried to do mapreduce to produce another collection with a new column to store the string length and then sort by that field. But the map reduce is not fast and take around 1000ms for 20,000 rows and I need to create a new collection everytime I do sorting. – Henry Liu Mar 19 '13 at 04:19
  • I second WiredPrairie's response - store the string length as a separate field in the document and sort on that. – shelman Mar 19 '13 at 16:58
  • probably a project and then sort – Abhi Jul 19 '14 at 03:31
  • Possible duplicate of [Select string length in mongodb](http://stackoverflow.com/questions/14647644/select-string-length-in-mongodb) – Vince Bowdren Apr 06 '17 at 12:01

2 Answers2

22

MongoDB 3.4 introduces the $strLenCP aggregation operator that finally supports this. An example:

db.collection.aggregate(
    [
        {$project: {
            "field": 1,
            "field_length": { $strLenCP: "$field" }
        }},
        {$sort: {"field_length": -1}},
        {$project: {"field_length": 0}}
    ]
)
M. Justin
  • 14,487
  • 7
  • 91
  • 130
Zohar Bar-Yehuda
  • 546
  • 4
  • 6
  • Can you show how its work in js code, i tried to do something similar but is doesn't work? – Qui-Gon Jinn Feb 08 '20 at 02:00
  • This worked like a charm; thanks [@zohar](https://stackoverflow.com/users/2017661/zohar-bar-yehuda) (Though as a MongoDB beginner myself, it might've been nice to note that the `"field"` key must refer to an _extant_ field in the document, and explain that the `"field_length"` key is customizable, since it's calculated on demand.) – Rafe Goldberg Mar 24 '20 at 19:42
  • 1
    Note the filed must exist otherwise it will cause `"errmsg" : "$strLenCP requires a string argument, found: missing",` – zhuguowei Apr 15 '20 at 02:46
  • If you want the whole document and not just a subset of the fields, I'd recommend the [`$addFields`](https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/#mongodb-pipeline-pipe.-addFields) operator rather than the first `$project` operator, as it will retain all existing fields in addition to adding the new one. `{$addFields: { "field_length": {$strLenCP: "$field"}}}` – M. Justin Jul 19 '21 at 18:02
-3

suppose your schema is something like: 

example = {_id: "XXX", text: "YYY"}

db.example.aggregate([
 {$project : {text : 1, length : {$size : "$text"}}}, 
 {$sort : {length : 1}}
]);

I think this will do the job, but only for mongo 2.6 and above

Marco
  • 207
  • 2
  • 8
  • 5
    $size is for arrays, not strings, so that will not work. – Wrench Jan 28 '15 at 19:28
  • uncaught exception: aggregate failed: { "errmsg" : "exception: The argument to $size must be an Array, but was of type: String", "code" : 17124, "ok" : 0 } – David Dec 16 '15 at 23:52