How to sort documents by custom (non-natural ordered) field

0
votes

I'm experimenting with MongoDB and its aggregation framework. With unwind operation I can split a document with an array field to multiple documents for each array index. Now if the array has data which has no natural sorting order, how can I sort this in MongoDB?

Example:

A products collection has the following documents (_id field omitted)

{ "type" : "t-shirt", "size" : [ "S", "M", "L" ] }
{ "type" : "pants", "size" : [ "XS", "S", "M", "L", "XL" ] }

After unwinding the collection data by size field, the results are

{ "type" : "t-shirt", "size" : "S" }
{ "type" : "t-shirt", "size" : "M" }
{ "type" : "t-shirt", "size" : "L" }
{ "type" : "pants", "size" : "XS" }
{ "type" : "pants", "size" : "S" }
{ "type" : "pants", "size" : "M" }
{ "type" : "pants", "size" : "L" }
{ "type" : "pants", "size" : "XL" }

The size field has no natural ordering, XL and XS for example are sorted next to each other, although they should be sorted to first/last position.

I migth get this solved by first projecting the each size to its corresponding number, i.e. XS=1, S=2... But is there any easier way to perform custom sorts?

1
mongodbmongodb-queryaggregation-frameworkmongodb-aggregation

1 Answers

1
votes

Yes there is no easier way but you have three ways to do it,

1) as you specified you can use $project and put put number 123 according to size and sort it on that numbers

2)you can sort on second character of the size string asc by substr 2 character and sorting it

3)use $text to sort on depending on its weight