MongoDB sorting does not work with inner array

Question

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?

Are you on Atlas v5.2? If so, $sortArray is what you need. If not, you will have to $unwind income_statement.annual, sort, then reassemble the array. Let us know which path works better for you. — Buzz Moschetti
– Buzz Moschetti, Commented Mar 29, 2022 at 17:00

ray · Accepted Answer · 2022-03-29 17:48:02Z

3

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.

edited Mar 29, 2022 at 17:48

answered Mar 29, 2022 at 17:04

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I. Shm Over a year ago

TY! Im only using v5.0.2, however, would you still provide a query with $sortArray in your answer for completeness? I will upgrade to 5.2+ later today.

ray Over a year ago

@I.Shm I do not have a MongoDB v5.2 handy, so I can only refer to the official example and try to construct the code. Updated the answer to contain the trial, you can have a try and modify it.

Collectives™ on Stack Overflow

MongoDB sorting does not work with inner array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related