MongoDB aggregate problem sort object fields

Question

I have the following document:

{
   "_id":{
      "$oid":"5e4d660e3b67ed11bce33b01"
   },
   "server-id":{
      "$numberLong":"137680026513571840"
   },
   "created":{
      "$date":"2020-02-19T16:45:02.536Z"
   },
   "prefixes":{
      "569494194964660224":{
         "displayname":"Admin",
         "placeholder":"%E2%9D%96"
      },
      "643867965313253385":{
         "displayname":"Mod",
         "placeholder":"%E2%9D%96"
      }
   },
   "module_permissions":{
      "ranking":true
   },
   "ranking":{
      "137679488392757248":15.039999999999997,
      "142705222618185728":6,
      "137865769668050945":0,
      "137913231757803520":10430.560000000001
   },
   "language":"de"
}

My problem is I will create an aggregate query that only outputs the ranking object elements with sorting. I have created the following aggregate query but how can I sort the elements of the object?

[{$match: {
  "server-id": 137680026513571840
}}, {$project: {
  "_id": 0, "ranking": 1
}}]

Expected output:

{
  "137913231757803520":10430.560000000001, 
  "137679488392757248":15.039999999999997,
  "142705222618185728":6,
  "137865769668050945":0
}

I expect a output like this {"137913231757803520":10430.560000000001,"137679488392757248":15.039999999999997,"142705222618185728":6,"137865769668050945":0} — Nisutec
– Nisutec, Commented May 17, 2020 at 18:53
I believe, ranking field should be an array, instead of object? — ngShravil.py
– ngShravil.py, Commented May 17, 2020 at 19:01

ngShravil.py · Accepted Answer · 2020-05-17 19:50:51Z

2

You can try the below:

db.collection.aggregate([
  { // Match the 'server-id'
    $match: {
      "server-id": 137680026513571840 
    }
  },
  { // Since your 'ranking' field is an array of heterogeneous objects, convert it to homogeneous array
    $project: {
      ranking: {
        $objectToArray: "$ranking"
      }
    }
  },
  { // Now it's an array, unwind it to individual documents
    $unwind: "$ranking"
  },
  { // Sort it based on the value
    $sort: {
      "ranking.v": -1
    }
  },
  { // Group by _id and the objects to 'ranking' array
    $group: {
      _id: "$_id",
      ranking: {
        $push: "$ranking"
      }
    }
  },
  { // Again convert back to heterogeneous array
    $project: {
      _id: 0,
      ranking: {
        $arrayToObject: "$ranking"
      }
    }
  }
])

It will give the following output:

{    
    "ranking" : {
        "137913231757803520" : 10430.560000000001,
        "137679488392757248" : 15.039999999999997,
        "142705222618185728" : 6,
        "137865769668050945" : 0
    }
}

MongoPlayGroundLink

The output is like above because, I don't know which all are the sub fields of ranking field. If, There are only these fields, then you can add one more aggregation stage in the pipeline like below:

{
  $project: {
    _id: 0,
    "137913231757803520": "$ranking.137913231757803520",
    "137679488392757248": "$ranking.137679488392757248",
    "142705222618185728": "$ranking.142705222618185728",
    "137865769668050945": "$ranking.137865769668050945",
     ... // Other fields to dsiplay
  }
}

Then the output will be like:

{
    "137913231757803520" : 10430.560000000001,
    "137679488392757248" : 15.039999999999997,
    "142705222618185728" : 6,
    "137865769668050945" : 0
}

MongoPlayGroundLink

I hope this is helpful.

Small suggestion: I don't recommend to store heterogeneous arrays, as it will be difficult to retrieve it.

answered May 17, 2020 at 19:50

ngShravil.py

5,0983 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ngShravil.py Over a year ago

@whoami, I am surprised to hear that, but it works in my machine.

ngShravil.py Over a year ago

Is there any source to get more information about this?

ngShravil.py Over a year ago

I didn't understand, what do yo mean by driver?

whoami - fakeFaceTrueSoul Over a year ago

Many comments :-) So making it short, I believe this sorting & winding to an object back will not be preserved by arrayToObject based on driver nd version. will ping the link if I can find it back..

ngShravil.py Over a year ago

@whoami, thanks for letting me know. Some minute things, which make huge difference, are always guided by experts.

|

Collectives™ on Stack Overflow

MongoDB aggregate problem sort object fields

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related