How do I order a list and add position according the user preference on MongoDB?-CodePudding

I have a collection in MongoDB and multiple users can access this data. Each user can save their sort ( position ) order. Next time data will load as per the saved sort order

Here is my collection

[{"_id":"62eca3abaeb0520009c0a1f4","Active":true,"name":"Sales", "orderBy":0},
{"_id":"62eca3b5aeb0520009c0a1f5","Active":true,"name":"Marketing","orderBy":1},
{"_id":"62eca3bfaeb0520009c0a1f6","Active":true,"name":"CRM","orderBy":2}]

Is there any way to keep the user sort position in the same record and get the recorded according to the user id like the below data structure

[{"_id":"62eca3abaeb0520009c0a1f4","Active":true,"name":"Sales",  position:[{userid:'1xxxxx',orderby:0},{userid:'2xxxxx',orderby:1}]},
{"_id":"62eca3b5aeb0520009c0a1f5","Active":true,"name":"Marketing",position:[{userid:'1xxxxx',orderby:2},{userid:'2xxxxx',orderby:1}},
{"_id":"62eca3bfaeb0520009c0a1f6","Active":true,"name":"CRM",position:[{userid:'1xxxxx',orderby:1},{userid:'2xxxxx',orderby:0}}]

Please share a solution thanks

CodePudding user response：

Does this helps?

db.collection.aggregate([
  {
    "$addFields": {
      "matchingUser": {
        "$arrayElemAt": [
          {
            "$filter": {
              "input": "$position",
              "as": "user",
              "cond": {
                "$eq": [
                  "$$user.userid",
                  "2xxxxx"
                ]
              }
            }
          },
          0
        ]
      }
    }
  },
  {
    "$sort": {
      "matchingUser.orderby": 1
    }
  },
  {
    "$project": {
      "matchingUser": 0
    }
  }
])

Playground link