what is the equivalent mongodb query for the following mysql query?

Question

what is the equivalent mongodb query for the following mysql query or how to write query to get the results

SELECT name, species FROM pet ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC

Currently MongoDB does not have the capacity to specify a sort like this, it is in the JIRA somewhere but currently the only way is really do this client side — Sammaye, Mar 14 '14 at 08:25
thanks for your response @Sammaye.. Is there anyway to do this using mongodb aggregation function? ... — vinoth kumar, Mar 14 '14 at 08:31
I don't think so, someone who has tried this before might know more than me but thinking about it I don't know of a good way — Sammaye, Mar 14 '14 at 08:36

score 0 · Accepted Answer · edited May 23 '17 at 12:13

0

What you need to do to achieve this is weight your responses using aggregate

db.collection.aggregate({
    { "$project": {
        "name": 1,
        "species", 1
        "weight": { "$cond": [
            { "$eq": [ "$species", "dog" ] },
            4,
            { "$cond": [
                { "$eq": [ "$species", "cat" ] },
                3,
                { "$cond": [
                    { "$eq": [ "$species", "snake" ] },
                    2,
                    { "$cond": [
                       { "$eq": [ "$species", "bird" ] },
                        1,
                        0
                    ]}
                ]}
            ]}
        ]}
    },
    { "$sort": { "weight": -1 }}
])

So you use the $cond operator which is a ternary operation to evaluate what the current value of the field is. If it matches the condition, assign the value to weight, if not move on to the next one, until if it did not match then assign 0.

Sort descending on the weight, and the results come out in the order you want.

You can see how to generate this nested structure in code here.

edited May 23 '17 at 12:13

Community

1
1

answered Mar 14 '14 at 09:44

Neil Lunn

148,042
36
346
317

thanks for your response. I hope it will help me. but my question will it affect the performance? – vinoth kumar Mar 14 '14 at 11:59
@vinothkumar What do you mean by performance? And correct me if I am wrong but your question reads as "how do I do this SQL" in mongoDB. The aggregation framework is **very** fast and much like the SQL you presented will be affected by a factor of how much data you actually have. The SQL you presented (where it is supported) affects performance. – Neil Lunn Mar 14 '14 at 12:03
I am not expert in DB management so i just wanted to know whether it will affect performance. Anyway i will use your example in my application. thank u so much.. – vinoth kumar Mar 14 '14 at 12:36
@vinothkumar The general protocol here is to ask a concise question, get responses, and if the response meets the needs of your question, then you accept it. Asking **additional** questions outside of the scope of your **actual** question is generally not "in the spirit" of the Q&A forum that SO is. I can say from experience the the SQL statement you presented **will** impact performance since the matching on terms excludes the use of an index in sort. The same can be said for the statement here. If you need further advice, then ask "another question". But your "question" has been answered. – Neil Lunn Mar 14 '14 at 12:44
@vinothkumar and your acceptance is missing why exactly? – Neil Lunn Mar 14 '14 at 12:54

what is the equivalent mongodb query for the following mysql query?

1 Answers1