Update documents field from the lowest to the biggest

Ops and Admin
,
1

current documents fetched by db.test.find({})

[
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "XYZ",
    "score": 123
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "ASD",
    "score": 12
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "YTR",
    "score": 98
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "LKJ",
    "score": 752
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "PLM",
    "score": 345
  }
]

And after executing this query db.test.idk({ idk }) against MongoDB the db.test.find() should return:

[
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "XYZ",
    "score": 123
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "ASD",
    "score": 752
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "YTR",
    "score": 345
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "LKJ",
    "score": 12
  },
  {
    "_id": "507f1f77bcf86cd799439011",
    "name": "PLM",
    "score": 98
  }
]

As you can see the scores are updated but now LKJ has the lowest score and ASD has the highest value. How can i do it?

  • Documents are just sample
  • Actual documents are too much
  • My though: I need alorithm and there is not any operator to help me to achieve it simpler.
2

It is not clear what you want to achieve but if you want to see your documents in a specific order you must sort them. If you want do see them in increasing order of score you do:

db.test.find({}).sort( { "score" : 1 } )
3

Let me clarify it more:

Introduction:

  • We had cities collection
  • Cities documents fields were: _id, name, stateId, createdAt, updatedAt
  • The business told us to sort cities based on the priority, And as you can see we had not priority field
  • So they list the cities that was important to them and assign each city a priority number. e.x. Beijing: 80, Chongqing: 39, Tianjin: 10, Shanghai: 67.
  • I write these query to do it: db.cities.update({ name: "Beijing" }, { $set: { priority: 80 } }), and the same query for other cities (I wrote an script to do that)
  • Now they want me to reverse those priority numbers. I mean they want to Beijing: have the lowest number, that it means Beijing’s priority should be 10 and Tianjin’s priority should be 80 and the same pattern applies to other documents.

Question:
Can I write a query to do that for me inside the mongo shell?

My Though:
No, It is impossible. I have to write an script to fetch all cities and then update their priority as I want

I did my best to clarify it as much as I could.

1 Like
4

That is quite clear now. You have moving requirements. A script or manual update is sometimes the easiest. It might be done with some aggregation, but coming up with the aggregation might involve more work. Unless the number of cities is high I would not bother.

But why change it. If you reverse the sort order you get what you want. Nothing stops you from interpreting high-priority being small priority number and a low-priority being a big priority number.

5

I have to update database, but there are too many cities and I have to do it with aggregation.

But I think I need something like $function . Am I right?

db.cities.aggregate([
    { 
        $function: {
            body: function(id, priority) {
                /* IDK, But I guess I have to write an algorithm here */
                /* But obviously this function executed for each document */
                /* Can I pass all records at once? */
            },
            args: [ "$_id", "$priority" ]
            lang: "js"
        }
    }
])
6

Here is how I would try to accomplish that.

  1. Get the list of ids from lowest priority number to highest
ids = c.find().sort( {priority:1} ).projection( {_id:1} ).toArray()
  1. Get the list of priorities from highest priority number to lowest
priorities = c.find().sort( {priority:-1} ).projection( {_id:0, priority:1} ).toArray()
  1. Join the 2 lists together
joined = []
for ( key of ids.keys() ) { joined[key] = { ...ids[key] , ...priorities[key] }}
  1. You can then take this joined array to create a bulk write to update each document with its new priority
1 Like
7

You absolutely can do this with an aggregation but the specification has to be absolutely precise. For example, to “reverse” the priority it could be exact same priorities in opposite order OR it could be the order is opposite but the priority score should be assigned based on some other algorithm.

You can update an existing collection using $merge aggregation stage, the question is how should the priorities be assigned to cities?

Here’s how you can reverse existing priorities exactly:

Sample input (there must be unique index on city):

db.cities.find()
{ "_id" : ObjectId("61ddc815d9acd4cb16c43f85"), "city" : "a", "priority" : 100 }
{ "_id" : ObjectId("61ddc81bd9acd4cb16c43f86"), "city" : "b", "priority" : 10 }
{ "_id" : ObjectId("61ddc822d9acd4cb16c43f87"), "city" : "c", "priority" : 17 }
{ "_id" : ObjectId("61ddc827d9acd4cb16c43f88"), "city" : "d", "priority" : 74 }

Pipeline:

db.cities.aggregate([
    {$sort:{priority:1}},
    {$group:{_id:null, cities:{$push:"$city"}, priority:{$push:"$priority"}}},
    {$project:{new:{$zip:{inputs:["$cities", {$reverseArray:"$priority"}]}}}}, 
    {$unwind:"$new"}, 
    {$replaceWith: {city:{$arrayElemAt:["$new", 0]}, priority:{$arrayElemAt:["$new", 1]}}},
    {$merge:{into:"cities", on:"city"}}
])

Result:

db.cities.find()
{ "_id" : ObjectId("61ddc815d9acd4cb16c43f85"), "city" : "a", "priority" : 10 }
{ "_id" : ObjectId("61ddc81bd9acd4cb16c43f86"), "city" : "b", "priority" : 100 }
{ "_id" : ObjectId("61ddc822d9acd4cb16c43f87"), "city" : "c", "priority" : 74 }
{ "_id" : ObjectId("61ddc827d9acd4cb16c43f88"), "city" : "d", "priority" : 17 }
1 Like
8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.