Mongo distinct query with sort not working

Tags: , , ,



I’m running a query in mongo using php like this:

$column ='address.roadname';



(new MongoDBClient())->db->mycollection->distinct($column,[],['$sort'=> [$column => 1]]);

The query gives results, but those are not sorted.
What am I missing?

The data are just strings “nested” into inner object
What I expect is a list of street names sorted alphabetically

sample data:

[
  {
    "name": "tizio",
    "address": [
      {
        "roadType": "via",
        "roadname": "Roma",
        "number": "12 bis",
        "city": "Milano"
      },
      {
        "roadType": "via",
        "roadname": "Emilia",
        "number": "124",
        "city": "Modena"
      },
      {
        "roadType": "via",
        "roadname": "Appia",
        "number": "89",
        "city": "Genova"
      }
    ]
  },
  {
    "name": "caio",
    "address": [
      {
        "roadType": "vicolo",
        "roadname": "stretto",
        "number": "12",
        "town": "Monza"
      },
      {
        "roadType": "largo",
        "roadname": "Garibaldi",
        "number": "24",
        "city": "Modena"
      },
      {
        "roadType": "piazza",
        "roadname": "Armi",
        "number": "26",
        "city": "Rovigo"
      }
    ]
  },
  {
    "name": "sempronio",
    "address": [
      {
        "roadname": "Roma",
        "number": "15",
        "city": "Milano"
      },
      {
        "roadType": "via",
        "roadname": "Po",
        "number": "4",
        "city": "Torino"
      },
      {
        "roadType": "largo",
        "roadname": "Garibaldi",
        "number": "9",
        "community": "Genova"
      }
    ]
  }
]

what I expect:

Appia,Armi,Emilia,Garibaldi,Po,Roma,Stretto

note: if I run it on mongo console

db.mycollection.distinct("address.roadname").sort() 

I got the expected result

Answer

The PHP implementation of distinct does have a sort option, see https://docs.mongodb.com/php-library/v1.7/reference/method/MongoDBCollection-distinct/

In your shell example,

db.mycollection.distinct("address.roadname")

returns an array, so when you run .sort() on that it is using the javascript Array.sort method.

The PHP distinct function also returns an array, so use the sort function on it.



Source: stackoverflow