In MongoDB,having a list of objects, sort all documents based on value of one field of an object by-CodePudding

I have a variable that I want to use in a query. I want to be able to sort all documents in a collection based on a value that is present in the same object as the key. For example I have 2 documents as follows:

Document 1:

{
  "_id": "Document 1",
  "data": {
    "list_of_data": [
      {
        "key": "KEY_1",
        "value": "value A",
        "valueId": "1234"
      },
      {
        "key": "KEY_2",
        "value": "value B",
        "valueId": "1235"
      },
      {
        "key": "KEY_3",
        "value": "value C",
        "valueId": "1236"
      }
    ]
  }
}

Document 2:

{
  "_id": "Document 2",
  "data": {
    "list_of_data": [
      {
        "key": "KEY_1",
        "value": "value B",
        "valueId": "1236"
      },
      {
        "key": "KEY_2",
        "value": "value A",
        "valueId": "1237"
      },
      {
        "key": "KEY_3",
        "value": "value C",
        "valueId": "1238"
      }
    ]
  }
}

My query takes in “key” data and I want to be able to sort all documents based on the “value” field in that particular object. For example, if I send “key” as “KEY_1” and I want it sorted in ascending order, the output should be the following:

[
  {
    "_id": "Document 1",
    "data": {
      "list_of_data": [
        {
          "key": "KEY_1",
          "value": "value A",
          "valueId": "1234"
        },
        {
          "key": "KEY_2",
          "value": "value B",
          "valueId": "1235"
        },
        {
          "key": "KEY_3",
          "value": "value C",
          "valueId": "1236"
        }
      ]
    }
  },
  {
    "_id": "Document 2",
    "data": {
      "list_of_data": [
        {
          "key": "KEY_1",
          "value": "value B",
          "valueId": "1236"
        },
        {
          "key": "KEY_2",
          "value": "value A",
          "valueId": "1237"
        },
        {
          "key": "KEY_3",
          "value": "value C",
          "valueId": "1238"
        }
      ]
    }
  }
]

This is because for “KEY_1” the first document has value A as compared to value B for the second document.

However, if I search for “KEY_2” in ascending order, the output should be as follows:

[
  {
    "_id": "Document 2",
    "data": {
      "list_of_data": [
        {
          "key": "KEY_1",
          "value": "value B",
          "valueId": "1236"
        },
        {
          "key": "KEY_2",
          "value": "value A",
          "valueId": "1237"
        },
        {
          "key": "KEY_3",
          "value": "value C",
          "valueId": "1238"
        }
      ]
    }
  },
  {
    "_id": "Document 1",
    "data": {
      "list_of_data": [
        {
          "key": "KEY_1",
          "value": "value A",
          "valueId": "1234"
        },
        {
          "key": "KEY_2",
          "value": "value B",
          "valueId": "1235"
        },
        {
          "key": "KEY_3",
          "value": "value C",
          "valueId": "1236"
        }
      ]
    }
  }
]

This is because for “KEY_2” the first document has value B as compared to value A for the second document.

Any ideas?

CodePudding user response：

Here's one way you could do it.

db.collection.aggregate([
  {
    "$set": {
      "sortVal": {
        "$reduce": {
          "input": "$data.list_of_data",
          "initialValue": "",
          "in": {
            "$cond": [
              {
                "$eq": [
                  "$$this.key",
                  "KEY_1"  // sort key
                ]
              },
              "$$this.value",
              "$$value"
            ]
          }
        }
      }
    }
  },
  { "$sort": { "sortVal": 1 } },
  { "$unset": "sortVal" }
])

Try it on mongoplayground.net.