Is it possible to sort case insensitive with Atlas Search?

[Renan_Geraldo]

Hello there,

I would like to know if it is possible to sort with case insensitive using Atlas Search Index. Ok, you will say “where is your $search?”. The search is optional, sometimes I don`t want to pass, but I really need to guarantee that always will be sorted by description in case insensitive (description is also the field that I am using in Atlas Search Index).

It would be perfect if I could achieve this, otherwise I will have to use project and lowercase all.

db.product.aggregate([
  {
    "$match": {
      "tenantId": "bbb60d4e-212f-445e-97a7-ddad13395931",
      "isArchive": false,
      "isActive": true
    }
  },
  {
    "$sort": {
      "description": 1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10
  }
])

[Jason_Tran]

Hi @Renan_Geraldo - Welcome to the community.

To better understand what you’re possibly after with Atlas search, are you able to give a few sample documents and expected outputs? It would also be great to see how you are currently achieving the desired results without use of Atlas search so that I further understand the context behind this question.

It would be perfect if I could achieve this, otherwise I will have to use project and lowercase all.

Also, just to clarify here, do you mean using $toLower when projecting so that your description field outputs are all lower case?

Regards,
Jason

[Renan_Geraldo]

Hi @Jason_Tran ,

Thank you so much for the response and the greetings.

Yes, I will contextualize better. First of all, this is the front end consuming my Api: https://notare.dev.qa.smartpos.net.br/ (I am sorry, it is in Portuguese, but I think it will be easy to explain). As you can see, it is a list of products. This req executes the query that I send above.

There is also a search bar. This search bar uses the same requisition, but it passes a query param called “description”. This is the query that will be executed using the Atlas search

db.product.aggregate([
  {
    "$search": {
      "autocomplete": {
        "path": "description",
        "query": "Nota"
      }
    }
  },
  {
    "$match": {
      "tenantId": "bbb60d4e-212f-445e-97a7-ddad13395931",
      "isArchive": false,
      "isActive": true
    }
  },
  {
    "$sort": {
      "description": 1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10
  }
])

So, sometimes I can do the query using the $search and sometimes not, but what I really want is to make every time the sorting to be case insensitive. So, I was trying to figure out if it is possible to do this with the Atlas search index. Passing the query in both manners, it is returning case sensitive.

Basically, this is the document:

{
  "_id": {
    "$oid": "62bb64108f4e7c44e778c81a"
  },
  "productCode": 430566,
  "tenantId": "bbb60d4e-212f-445e-97a7-ddad13395931",
  "codAlfa": "1",
  "description": "Caderno preto "
}

And for the second question, yes I would use the $toLower, but it would not return the lower field to the final user, I would just use to sort.

[Renan_Geraldo]

Hi,

I could achieve it with the collation.

db.product.aggregate([
  {
    "$match": {
      "tenantId": "bbb60d4e-212f-445e-97a7-ddad13395931",
      "isArchive": false,
      "isActive": true
    }
  },
  {
    "$sort": {
      "featuredPosition": 1,
      "description": 1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10
  }
], {collation: {
   locale: "pt"
}})