Question 224571 in Mongodb Question posted in
The official documentation can be found here.

How to sort fields of a document based on the nested position value in MongoDb

Say, I have two documents in a collection stored in the MongoDB

{
    _id: ObjectId("64cfe7bf9f45fdfdb2eadcb2"),
    userId: 101,
    title: { value: 'mr', isSelected: true, position: 1 },
    firstName: { value: 'John', isSelected: true, position: 4 },
    middleName: { value: 'M.', isSelected: true, position: 2 },
    lastName: { value: 'Doe', isSelected: true, position: 3 }
  },
  {
    _id: ObjectId("64cfe7bf9f45fdfdb2eadcb3"),
    userId: 102,
    title: { value: 'ms', isSelected: true, position: 1 },
    firstName: { value: 'Janna', isSelected: true, position: 2 },
    middleName: { value: 'R', isSelected: true, position: 3 },
    lastName: { value: 'Lee', isSelected: true, position: 4 }
  }

I will pass the userId value to the query and in return I wanted the document to be returned with the document’s fields in an order defined in the position value.

Expected Result if userId is 101:

{
    _id: ObjectId("64cfe7bf9f45fdfdb2eadcb2"),
    userId: 101,
    title: { value: 'mr', isSelected: true, position: 1},
    middleName: { value: 'M.', isSelected: true, position: 2},
    lastName: { value: 'Doe', isSelected: true, position: 3}
    firstName: { value: 'John', isSelected: true, position: 4},
    }

Note that field names are in different order (title, middleName, lastName, firstName).

And, if userID is 102 then:

{
    _id: ObjectId("64cfe7bf9f45fdfdb2eadcb3"),
    userId: 102,
    title: { value: 'ms', isSelected: true, position: 1},
    firstName: { value: 'Janna', isSelected: true, position: 2},
    middleName: { value: 'R', isSelected: true, position: 3},
    lastName: { value: 'Lee', isSelected: true, position: 4}
    }

Notice the field names are in different order than the first query (title, firstName, middleName, lastName)

Thank you

Tags:

2

Answers


  1. 0

    To achieve the expected result, you can use MongoDB’s aggregation framework to sort the fields based on the position value. Here’s a sample query to accomplish this:

    const userId = 101; // Replace this with the desired userId value

db.collection.aggregate([
  { $match: { userId: userId } },
  { $project: {
    _id: 1,
    userId: 1,
    title: 1,
    firstName: 1,
    middleName: 1,
    lastName: 1,
    fieldOrder: {
      $function: {
        body: function() {
          return ["title", "middleName", "lastName", "firstName"];
        },
        args: [],
        lang: "js"
      }
    }
  }},
  { $project: {
    _id: 1,
    userId: 1,
    fieldOrder: 1,
    documentFields: {
      $objectToArray: "$$ROOT"
    }
  }},
  { $unwind: "$documentFields" },
  { $match: { $expr: { $in: ["$documentFields.k", "$fieldOrder"] } } },
  { $group: {
    _id: { _id: "$_id", userId: "$userId" },
    fields: { $push: "$documentFields" }
  }},
  { $replaceRoot: {
    newRoot: { $arrayToObject: "$fields" }
  }}
])

    Explanation of the query:

    1. The $match stage filters the documents based on the userId.
    2. The first $project stage selects the desired fields (_id, userId, title, firstName, middleName, lastName) and creates a field called fieldOrder which holds an array defining the order of the fields.
    3. The second $project stage converts the document into an array of key-value pairs using $objectToArray.
    4. The $unwind stage deconstructs the array created in the previous stage.
    5. The second $match stage filters the unwound key-value pairs to only include those whose key ($documentFields.k) is present in the fieldOrder array.
    6. The $group stage groups the key-value pairs back into an array using the userId and _id.
    7. The final $replaceRoot stage promotes the array of key-value pairs back to an object, effectively reshaping the document with fields sorted based on the position.
    Login or Signup to reply.
  2. 0

    If I understand what you are trying to do (I usually avoid any object field ordering importance.), this aggregation pipeline may be sufficient. It’s a bit specific and not flexible, so it would need adjustment if the document model changes.

    db.collection.aggregate([
  {
    "$match": {
      "userId": 101  // "I will pass the userId value to the query..."
    }
  },
  { // rewrite/replace entire document
    "$replaceWith": {
      "$mergeObjects": [
        // object of everything except the fields that are sorted
        {
          "_id": "$_id",
          "userId": "$userId"
        },
        // object of fields ordered by position
        {
          "$arrayToObject": {
            "$sortArray": {
              "input": [
                {"k": "title", "v": "$title"},
                {"k": "firstName", "v": "$firstName"},
                {"k": "middleName", "v": "$middleName"},
                {"k": "lastName", "v": "$lastName"},
              ],
              "sortBy": {"v.position": 1}
            }
          }
        }
      ]
    }
  }
])

    N.B.: Unfortunately the MongoDB Go driver that mongoplayground.net uses always does an alphanumeric sort of objects by field names, so demonstrating this there is problematic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search