Question 361762 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to sort by the smallest value from multiple fields?

I have list of mongo documents

[
  {
    "created_at": "2024-03-15T18:11:18.687",
    "updated_at": "2024-03-15T18:11:28.945",
    "fields": {
      "contract_first": [],
      "contract_second": [
        {
          "position": "Bob"
        }
      ],
      "uuid": "bf031b46-ec33-4c61-ad33-a3ad2cbeb9cb"
    },
    "id": "65f46520928f6fd496ffbe6d"
  },
  {
    "created_at": "2024-03-15T18:11:18.687",
    "updated_at": "2024-03-15T18:15:26.838",
    "fields": {
      "contract_first": [
        {
          "position": "Anna"
        }
      ],
      "contract_second": [],
      "uuid": "bf052b46-ec33-4c61-ad33-a3ad2cbeb9cb"
    },
    "id": "65f4660e928f6fd496ffbeb0"
  },
  {
    "created_at": "2024-03-15T18:11:18.687",
    "updated_at": "2024-03-15T18:15:26.838",
    "fields": {
      "contract_first": [
        {
          "position": "Bob"
        }
      ],
      "contract_second": [],
      "uuid": "bf092b46-ec33-4c61-ad33-a3ad2cbeb9cb"
    },
    "id": "65f8660e928f6fd496ffbeb0"
  }
]

Which has 2 main array fields contract_first and contract_second and i want to know how to sort my document by 2 that array fields?

I was trying to use

{
  '$sort': {
    'fields.contract_first.position': 1,
    'fields.contract_second.position': 1
  }
}

But I have got an error cannot sort with keys that are parallel arrays

For example Output should be like:
Anna
Bob
Bob
even if they are in different fields

Tags:

2

Answers


  1. 0

    Wrt wanting "Dave" before "Ellen" even in the case of { contract_first: [{ position: "Ellen" }], contract_second: [] } vs { contract_first: [], contract_second: [{ position: "Dave" }] }

    If only first or second contract will have an entry but not both, then you can do this:
    Assign a sort_key based on the first non-null value of each, and then sort on that value.

    db.collection.aggregate([
  {
    $set: {
      sort_key: {
        $ifNull: [
          { $first: "$fields.contract_first.position" },
          { $first: "$fields.contract_second.position" }
        ]
      }
    }
  },
  { $sort: { sort_key: 1 } }
])

    However, in cases where both first & second contract can exist, you’ll need to re-add the 2nd contract as a sort_key.

    So here, I’ve created two of them: sort_key1 is the first non-null contract with a position between first_contract & second_contract and sort_key2 is only the second_contract. So sort_key2 would get used when sort_key1 is the same.

    db.collection.aggregate([
  {
    $set: {
      sort_key1: {
        $ifNull: [
          { $first: "$fields.contract_first.position" },
          { $first: "$fields.contract_second.position" }
        ]
      },
      sort_key2: { $first: "$fields.contract_second.position" }
    }
  },
  {
    $sort: {
      sort_key1: 1,
      sort_key2: 1
    }
  },
  {
    // comment this out to see the sort_keys
    $unset: ["sort_key1", "sort_key2"]
  }
])

    Mongo Playground with better data examples.

    Login or Signup to reply.
  2. 0

    Since mongoDB version 5.2, one option is:

    db.collection.aggregate([
  {$set: {sort_key: {$sortArray: {
          input: {
            $concatArrays: [
              "$fields.contract_first.position",
              "$fields.contract_second.position"
            ]
          },
          sortBy: 1
  }}}},
  {$sort: {sort_key: 1}},
  {$unset: "sort_key"}
])

    See How it works on the mongoDB playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search