Reputation: 617

Sort in mongoDB using a specific order

I am currently looking to sort the sub document, Clients, in a particular order based on an array.

The mongoDB structure is

{
    "_id" : "1033",
    "Name" : "Test",
    "Clients" : [ 
        {
            "Id" : 1033,
            "Types" : [ 
                {
                    "Class" : "C",
                    "Service" : null
                },
                {
                    "Class" : "B",
                    "Service" : null
                }
            ]
        }, 
        {
            "Id" : 156136,
            "Types" : [ 
                {
                    "Class" : "A",
                    "Service" : null
                }, 
                {
                    "Class" : "B",
                    "Service" : null
                }, 
                {
                    "Class" : "C",
                    "Service" : null
                }, 
                {
                    "Class" : "D",
                    "Service" : null
                }
            ]
        }
    ]
}

I need the above document displayed in the order based on a array like [B, A, D, C]

So that the output would be as below:

{
    "_id" : "1033",
    "Name" : "Test",
    "Clients" : [ 
        {
            "Id" : 1033,
            "Types" : [ 
                {
                    "Class" : "B",
                    "Service" : null
                },
                {
                    "Class" : "C",
                    "Service" : null
                }
            ]
        }, 
        {
            "Id" : 156136,
            "Types" : [ 
                {
                    "Class" : "B",
                    "Service" : null
                }, 
                {
                    "Class" : "A",
                    "Service" : null
                }, 
                {
                    "Class" : "D",
                    "Service" : null
                }, 
                {
                    "Class" : "C",
                    "Service" : null
                }
            ]
        }
    ]
}

Could you please help me on how to achieve this?

I am currently using MongoDB Driver for .Net

Upvotes: 8

Answers (3)

tlejmi

Reputation: 596

I've solved my problem with custom sorting , using $addFields and $indexOfArray aggregations.

let imagine that my document has two fields: BouqetId, Name

[
    { "BouqetId" : 2, "Name" : "Name2"},
    { "BouqetId" : 16, "Name" : "Name16"},
    { "BouqetId" : 25, "Name" : "Name25"},
    { "BouqetId" : 15, "Name" : "Name15"},
    { "BouqetId" : 125, "Name" : "Name125"},
    { "BouqetId" : 258, "Name" : "Name258"},
    { "BouqetId" : 127, "Name" : "Name127"}
            ... 
  ]

and I want to search for Bouqet with Ids [258,15,2,16] and get them with this order.

1/ I filter my collection using $in operator in $match aggregation to get the required documents: view step one in code.

2/ I add a field using $addFields aggregation named Order and assign to it the index of the BouqetId in the searched array using $indexOfArray aggregation.

3/ finally I sort them using the newly added Order field.

4/ I get my custom-ordered result. I can remove the order field but it is ok for now.

here and exmaple in Nodejs :

var db = client.db("MyDatabase");
var collection = db.collection("Bouqets");
var pipeline = [
    {
        "$match": {
            "BouqetId": {
                "$in": [
                    258,
                    15,
                    2,
                    16
                ]
            }
        }
    }, 
    {
        "$addFields": {
            "Order": {
                "$indexOfArray": [
                    [
                       258,
                       15,
                       2,
                       16
                       ],
                    "$BouqetId"
                ]
            }
        }
    }, 
    {
        "$sort": {
            "Order": 1.0
        }
    }
];

var cursor = collection.aggregate(pipeline);

Here my result :

[
    { "BouqetId" : 258, "Name" : "Name258" , "Order" : 0},
    { "BouqetId" : 15, "Name" : "Name15", "Order" : 1},
    { "BouqetId" : 2, "Name" : "Name2", "Order" : 2},
    { "BouqetId" : 16, "Name" : "Name16", "Order" : 3}
]

here the same example in c# :

       IMongoClient client = new MongoClient("mongodb://host:port/");
       IMongoDatabase database = client.GetDatabase("MyDatabase");
       IMongoCollection<BsonDocument> collection = database.GetCollection<BsonDocument>("Bouqets");

       var options = new AggregateOptions()
       {
           AllowDiskUse = false
       };

       // my array 
       var searchArray = new int[] {258, 15, 2, 16}; 


       PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
       {
            new BsonDocument("$match", new BsonDocument()
                .Add("BouqetId", new BsonDocument()
                    .Add("$in", new BsonArray(searchArray)
                    )
                )),
            new BsonDocument("$addFields", new BsonDocument()
                .Add("Order", new BsonDocument()
                    .Add("$indexOfArray", new BsonArray()
                        .Add(new BsonArray(searchArray)
                        )
                        .Add("$BouqetId")
                    )
                )),
            new BsonDocument("$sort", new BsonDocument()
                .Add("Order", 1.0))
       };

       var result = collection.Aggregate(pipeline, options).ToList();

Upvotes: 4

Mahesh

Reputation: 610

Custom ordering is possible via aggregation as specified here.

Upvotes: 5

Ramraj Patel

Reputation: 193

Custom ordering is not possible in MongoDB. You can either sort ascending or descending.

Upvotes: 0

Sort in mongoDB using a specific order

Answers (3)

Related Questions