MongoDB – Sort Array Objects Within Array Using Expression

arraymongodbquerysorting

Please help to make query in mongodb.
I want mongo query in which sort array objects using some expression.
Example.
I have documents like

                {
                "_id:"5e0846c922427bdc824d3943",
                "isActive" : true,
                "customFieldArray" : [
                    {
                        "custom_field_id" : "fname",
                        "field_value" : "kevin"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "patel"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "surat"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Boy"

            },
            {
                "_id":"5e08471d6053718c808cbab8",
                "isActive" : true,
                "customFieldArray" : [
                     {
                        "custom_field_id" : "fname",
                        "field_value" : "alice"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "somani"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "bharuch"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Boy"

            },
            {
                "_id":"5e0847392b8622f8100267e3",
                "isActive" : true,
                "customFieldArray" : [
                     {
                        "custom_field_id" : "fname",
                        "field_value" : "Isha"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "Trivedi"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "Mumbai"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Girl"

            }

and when pass filter parameter on custom_field_id = "fname"
then it will display results in following ascending or descending way.

                {
                "_id":"5e08471d6053718c808cbab8",
                "isActive" : true,
                "customFieldArray" : [
                     {
                        "custom_field_id" : "fname",
                        "field_value" : "alice"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "somani"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "bharuch"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Boy",

            },{
                "_id":"5e0847392b8622f8100267e3",
                "isActive" : true,
                "customFieldArray" : [
                     {
                        "custom_field_id" : "fname",
                        "field_value" : "Isha"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "Trivedi"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "Mumbai"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Girl",

            },
            {
                "_id:"5e0846c922427bdc824d3943",
                "isActive" : true,
                "customFieldArray" : [
                    {
                        "custom_field_id" : "fname",
                        "field_value" : "kevin"
                    },
                    {
                        "custom_field_id" : "lname",
                        "field_value" : "patel"
                    },
                    {
                        "custom_field_id" : "city",
                        "field_value" : "surat"
                    }
                ],
                "creationMode" : "adminForm",
                "createdAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "updatedAt" : ISODate("2019-11-21T06:40:20.033Z"),
                "gender" : "Boy"

            }

Best Answer

Aggregation sorts the documents based on the inputs INPUT_EXPRESSION and SORT_ORDER. The input expression is matched with the customFieldArray's custom_field_id value and the documents are sorted based on the corresponding field_value.

var INPUT_EXPRESSION = "fname";    // values can be "fname", "city" or "lname" 
var SORT_ORDER = 1;    // values can be 1 (ascending) or -1 (descending)

db.test.aggregate( [
  { 
      $addFields: { 
          customFieldTemp: {
              $filter: {
                   input: "$customFieldArray",
                      as: "arr",
                    cond: {
                             $eq: [ "$$arr.custom_field_id", INPUT_EXPRESSION ]
                    }
              }
          }
      } 
  },
  { 
      $addFields: { 
          customFieldTemp: { $arrayElemAt: [ "$customFieldTemp", 0 ] }
      } 
  },
  { 
      $sort: { "customFieldTemp.field_value" : SORT_ORDER } 
  },
  { 
      $project: { customFieldTemp: 0 } 
  }
] )


Example:

From the sample input documents in the question post, if the INPUT_EXPRESSION = "city" and SORT_ORDER = 1, the documents are sorted as: _id: "5e08471d6053718c808cbab8" ("bharuch"), _id: "5e0847392b8622f8100267e3" ("mumbai") and _id: "5e0846c922427bdc824d3943" ("surat").

Related Question