Sort on two fields in MongoDB simultaneously

Question

I am currently developing a solution in MongoDB, where i have two different data schemas in the same collection. The problem is that some of the data has different field names. For example: In one of the contracts a date is named: date_at, but in another type of contract it is just named date.

So the question is: How do i sort on one field, and if it is not present in the document, sort on the other instead?

Currently i am trying to make a query such as this:

db.collection('contracts').aggregate([
  {$project: {
    date_at: [ $ifNull: ['$date', null]
  }},
  {$sort: { date_at: -1 }}
]);

But it doesn't seem to work. Hopefully one of you will be able to help me. Thank you in advance.

matthPen · Accepted Answer · 2019-03-19 16:50:51Z

1

$ifNull is your solution :

taking the following documents set :

[
  {
    _id: 1,
    name: "c"
  },
  {
    _id: 2,
    name: "a"
  },
  {
    _id: 3,
    lastname: "b"
  },
  {
    _id: 4,
    name: "d"
  },
]

The following query will project name, or lastname if name doesn't exist, as name, and sort by name on second stage :

db.collection.aggregate([
  {
    $project: {
      name: {
        $ifNull: [
          "$name",
          "$lastname"
        ]
      }
    }
  },
  {
    $sort: {
      name: 1
    }
  }
])

Will result in :

[
  {
    "_id": 2,
    "name": "a"
  },
  {
    "_id": 3,
    "name": "b"
  },
  {
    "_id": 1,
    "name": "c"
  },
  {
    "_id": 4,
    "name": "d"
  }
]

$ifNull takes an array of 2 params : if first is null or not present, then use the second.

answered Mar 19, 2019 at 16:50

matthPen

4,3731 gold badge18 silver badges18 bronze badges

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

1 Comment

Jonas Hoffmann Over a year ago

Thanks a lot dude! I couldn't quite figure it out!

matwr · Accepted Answer · 2019-03-19 16:47:41Z

0

It sounds like you need to create a compound index.

A compound index consists of a single index which references multiple fields.

NOTE. It is important that the order of the fields in a compound index correspond to the order logic of your query's sort (see Sort Order).

Example:

Create compound index:

db.data.createIndex( { a:1, b: 1, c: 1, d: 1 } )

Find data with sort:

db.data.find().sort( { a: -1, b: -1 } )

More Info:

https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/#sort-on-multiple-fields

https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/

answered Mar 19, 2019 at 16:47

matwr

1,5811 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

Sort on two fields in MongoDB simultaneously

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related