MongoDB sorting does not work with inner array-CodePudding

I'm trying to query specific fields in my document and sort them by one of the fields, however, the engine seems to completely ignore the sort.

I use the query:

     db.symbols.find({_id:'AAPL'}, {'income_statement.annual.totalRevenue':1,'income_statement.annual.fiscalDateEnding':1}).sort({'income_statement.annual.totalRevenue': 1})

This is the output:

    [
      {
        _id: 'AAPL',
        income_statement: {
          annual: [
            {
              fiscalDateEnding: '2021-09-30',
              totalRevenue: '363172000000'
            },
            {
              fiscalDateEnding: '2020-09-30',
              totalRevenue: '271642000000'
            },
            {
              fiscalDateEnding: '2019-09-30',
              totalRevenue: '256598000000'
            },
            {
              fiscalDateEnding: '2018-09-30',
              totalRevenue: '265595000000'
            },
            {
              fiscalDateEnding: '2017-09-30',
              totalRevenue: '229234000000'
            }
          ]
        }
      }
    ]

I would expect to have the entries sorted by fiscalDateEnding, starting with 2017-09-30 ascending. However, the order is fixed, even if I use -1 for sorting.

Any ideas?

CodePudding user response：

The sort you are using is for the ordering of documents in the result set. This is different from the ordering of array elements inside the document.

For your case, if you are using a newer version of MongoDB (5.2 ), you can use the $sortArray.

db.symbols.aggregate([
  {
    $project: {
      _id: 1,
      annual: {
        $sortArray: {
          input: "$income_statement.annual",
          sortBy: {
            fiscalDateEnding: 1
          }
        }
      }
    }
  }
])

If you are using older version of MongoDB, you can do the followings to perform the sorting.

db.collection.aggregate([
  {
    "$unwind": "$income_statement.annual"
  },
  {
    $sort: {
      "income_statement.annual.fiscalDateEnding": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      annual: {
        $push: "$income_statement.annual"
      }
    }
  },
  {
    "$project": {
      _id: 1,
      income_statement: {
        annual: "$annual"
      }
    }
  }
])

Here is the Mongo Playground for your reference.