0

Say I have a mongodb collection with the below schema

{ "_id" : "1", "name" : "person1", "description" : "description1" }
{ "_id" : "2", "name" : "person2", "description" : "description2" }
{ "_id" : "3", "name" : "person3", "description" : "description3" }
{ "_id" : "4", "name" : "person1", "description" : "description4" }
{ "_id" : "5", "name" : "person2", "description" : "description5" }
{ "_id" : "6", "name" : "person4", "description" : "description6" }
{ "_id" : "7", "name" : "person3", "description" : "description7" }
{ "_id" : "8", "name" : "person2", "description" : "description8" }
{ "_id" : "9", "name" : "person3", "description" : "description9" }
{ "_id" : "10", "name" : "person1", "description" : "description10" }

I want to sort the rows by person name values in the below order

  • person4
  • person2
  • person1
  • person3

    how can I turn them into this form?

    { "_id" : "6", "name" : "person4", "description" : "description6" }
{ "_id" : "2", "name" : "person2", "description" : "description2" }
{ "_id" : "8", "name" : "person2", "description" : "description8" }
{ "_id" : "5", "name" : "person2", "description" : "description5" }
{ "_id" : "1", "name" : "person1", "description" : "description1" }
{ "_id" : "4", "name" : "person1", "description" : "description4" }
{ "_id" : "10", "name" : "person1", "description" : "description10" }
{ "_id" : "7", "name" : "person3", "description" : "description7" }
{ "_id" : "3", "name" : "person3", "description" : "description3" }
{ "_id" : "9", "name" : "person3", "description" : "description9" }

    Below is the same scenario in SQL world

    +----+---------+---------------+
| id | name    | description   |
+----+---------+---------------+
|  1 | person1 | description1  |
|  2 | person2 | description2  |
|  3 | person3 | description3  |
|  4 | person1 | description4  |
|  5 | person2 | description5  |
|  6 | person4 | description6  |
|  7 | person3 | description7  |
|  8 | person2 | description8  |
|  9 | person3 | description9  |
| 10 | person1 | description10 |
+----+---------+---------------+

    so I use this query

    select id, name, description
from test
order by case when name like 'person4' then 1
when name like 'person2' then 2
when name like 'person1' then 3
when name like 'person3' then 4 end

    that gives me the right result

    +----+---------+---------------+
| id | name    | description   |
+----+---------+---------------+
|  6 | person4 | description6  |
|  2 | person2 | description2  |
|  8 | person2 | description8  |
|  5 | person2 | description5  |
|  1 | person1 | description1  |
|  4 | person1 | description4  |
| 10 | person1 | description10 |
|  7 | person3 | description7  |
|  3 | person3 | description3  |
|  9 | person3 | description9  |
+----+---------+---------------+

    do we have an equivalent to the SQL order by case when then statement? or any solution to accomplish that in mongodb?

    • Jad Chahine
    • 6,849
    • 8
    • 37
    • 59
    • @Neil Lunn: thanks. The solution is this `db.testing.aggregate({$match:{"name":{$in:["person4", "person2", "person1", "person3"]}}}, {$addFields:{__order: { $indexOfArray : [ ["person4", "person2", "person1", "person3"], "$name" ]}}}, {$sort:{ "__order":1}});` – Jad Chahine Jul 14 '17 at 10:21
    • Pretty much, yeah. Bottom line is to supply the required "order" in some manner as input to the query. – Neil Lunn Jul 14 '17 at 10:24
    • yes, thank you. One more question, this works on mongo 3.4 but it is not working on past versions. Do we have a similar solution for older versions of mongo? – Jad Chahine Jul 14 '17 at 10:29
    • Yup. There's a really old answer of mine there. Everyone has been adding to the answers ever since. The original works from MongoDB 2.2 – Neil Lunn Jul 14 '17 at 10:30
    • yes i got it now (Y) – Jad Chahine Jul 14 '17 at 10:53

    0 Answers0