1

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"

            }
4
  • What is the MongoDB Version(x,y,z)? Dec 24, 2019 at 6:00
  • @MdHaidarAliKhan MongoDB version 3.6
    – 5a01d01P
    Dec 24, 2019 at 6:16
  • Does your example represent three documents or a single document with three documents embedded? It would be helpful to include top-level _ids for clarity on the document structure.
    – Stennie
    Dec 28, 2019 at 22:49
  • @Stennie Question is updated.
    – 5a01d01P
    Dec 29, 2019 at 6:31

2 Answers 2

1

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").

0

As per MongoDB documentation here The $elemMatch operator limits the contents of an field from the query results to contain only the first element matching the $elemMatch condition.

For example

db.collectionName.aggregate({$match: {'customFieldArray.custom_field_id' : 'lname'}})

For further your ref MongoDB find() not getting anything, MongoDB How to query data in object with random names?

3
  • Thanks but here I need sorting based on customFieldArray.custom_field_id value. If I pass customFieldArray.custom_field_id : lname then customFieldArray.field_value it's values will be sorted.
    – 5a01d01P
    Dec 24, 2019 at 9:24
  • @5a01d01P, As you have already said that you want to filter that's why i have been written the syntax according to your query desire. Now you want to sort according to your desire field value customFieldArray.custom_field_id . Right Dec 24, 2019 at 9:43
  • Yes, I need sorting but it's not like normal sorting. please go through my question I explained in detail, how results will be fetched on the filter and sorted.
    – 5a01d01P
    Dec 24, 2019 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.