How to sort a collection based on a member of array-CodePudding

I need to sort the document based on price and price is an array while we are sorting price based on the key PRICE_A, PRICE_B or PRICE_C. sample Data:

[
  {
    "_id": 1,
    "price": [
      {
        "PRICE_A": 3098.67
      },
      {
        "PRICE_B": 3166.67
      },
      {
        "PRICE_C": 3800
      }
    ]
  },
  {
    "_id": 2,
    "price": [
      {
        "PRICE_A": 679.6
      },
      {
        "PRICE_B": 781.6
      },
      {
        "PRICE_C": 938
      }
    ]
  }
]

expected output: by ascending order PRICE_A

[
   {
      "_id":2,
      "price":[
         {
            "PRICE_A":679.6
         },
         {
            "PRICE_B":781.6
         },
         {
            "PRICE_C":938
         }
      ]
   },
   {
      "_id":1,
      "price":[
         {
            "PRICE_A":3098.67
         },
         {
            "PRICE_B":3166.67
         },
         {
            "PRICE_C":3800
         }
      ]
   }
]

I am using this query- https://mongoplayground.net/p/Ajaw4SFVpzg

CodePudding user response：

There is no straight way to do this, you need to improve your schema design otherwise this query will impact performance,

$addFields to create a new custom field p
$arrayElemAt to get first price value by passing its key name, here you have to pass your dynamic price key name
$sort by p in ascending order
$unset remove p key because it is not needed now

  {
    "$addFields": {
      "p": { "$arrayElemAt": ["$price.PRICE_A", 0] }
    }
  },
  { "$sort": { "p": 1 } },
  { "$unset": "p" }

Playground