[SERVER-76979] $sortArray does not provide correct results in case of case-sensitive data Created: 10/May/23  Updated: 18/May/23  Resolved: 11/May/23

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 6.0.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Amanpreet Singh Assignee: Backlog - Triage Team
Resolution: Done Votes: 0
Labels: Bug
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File Screenshot 2023-05-10 at 2.23.59 PM.png    
Assigned Teams:
Server Triage
Operating System: ALL
Steps To Reproduce:

// Insert records

db.engineers.insertOne(
   {
      "team":
         [
            {
              "name": "pat",
              "age": 30,
              "address": { "street": "12 Baker St", "city": "London" }
            },
            {
              "name": "dallas",
              "age": 36,
              "address": { "street": "12 Cowper St", "city": "Palo Alto" }
            },
            {
              "name": "charlie",
              "age": 42,
              "address": { "street": "12 French St", "city": "New Brunswick" }
            },
            {
              "name": "Dallas",
              "age": 36,
              "address": { "street": "12 Cowper St", "city": "Palo Alto" }
           }
        ]
})

sort on a field:

db.engineers.aggregate( [
   { $project:
      {
          _id: 0,
          result:
            {
               $sortArray: { input: "$team", sortBy: { name: 1 } }
            }
      }
   }
] )

// Result of the above query 

 

[
  {
    "result": [
      {
        "address": {
          "city": "Palo Alto ",
          "street": "12 Cowper St"
        },
        "age": 36,
        "name": "Dallas"
      },
      {
        "address": {
          "city": "New Brunswick",
          "street": "12 French St"
        },
        "age": 42,
        "name": "charlie"
      },
      {
        "address": {
          "city": "Palo Alto",
          "street": "12 Cowper St"
        },
        "age": 36,
        "name": "dallas"
      },
      {
        "address": {
          "city": "London",
          "street": "12 Baker St"
        },
        "age": 30,
        "name": "pat"
      }
    ]
  }
]

Participants:

 Description   

The sorting of array data using `$sortArray` is not correct. 

You can check the link - https://mongoplayground.net/p/c61hwM85OG1



 Comments   
Comment by Amanpreet Singh [ 18/May/23 ]

Thanks, Eric for the update. 
It seems to be working fine using `collation`

Can you please let me know, how we can improve the MongoDB docs and present this as an example?

Comment by Eric Sedor [ 11/May/23 ]

Hi dalmi.aman@gmail.com

$sortArray is lexicographic by default. You'll need to specify a collation:

> db.engineers.aggregate( [ { $project: { _id: 0, result: { $sortArray: { input: "$team", sortBy: { name: 1 } } } } } ] , {collation:{caseLevel: true, locale: "en", strength:1}}).pretty()
{
	"result" : [
		{
			"name" : "charlie",
			"age" : 42,
			"address" : {
				"street" : "12 French St",
				"city" : "New Brunswick"
			}
		},
		{
			"name" : "dallas",
			"age" : 36,
			"address" : {
				"street" : "12 Cowper St",
				"city" : "Palo Alto"
			}
		},
		{
			"name" : "Dallas",
			"age" : 36,
			"address" : {
				"street" : "12 Cowper St",
				"city" : "Palo Alto"
			}
		},
		{
			"name" : "pat",
			"age" : 30,
			"address" : {
				"street" : "12 Baker St",
				"city" : "London"
			}
		}
	]
}

For further questions about crafting the right query for your use-case I encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums.

  • If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project.
  • Or, if you identify a feature you really need, I'd suggest making that request using our MongoDB Feedback Engine.

Thanks,
Eric

Comment by Yuan Fang [ 11/May/23 ]

Hi dalmi.aman@gmail.com,

Thank you for your report. It appears that the result of sorting by name in ascending order is correct, as the names are sorted as "Dallas", "charlie", "dallas", and "pat". Could you please elaborate on why you believe the result is incorrect?

Regards,
Yuan

Generated at Thu Feb 08 06:34:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.