DataStore case insensitive query not possible??

[sacrampton]

Before opening, please confirm:

• I have searched for duplicate or closed issues and discussions.
• I have read the guide for submitting bug reports.
• I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.

JavaScript Framework

React Native

Amplify APIs

DataStore

Amplify Categories

api

Environment information

# Put output below this line
npx: installed 1 in 1.188s

  System:
    OS: Linux 4.14 Amazon Linux 2
    CPU: (2) x64 Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz
    Memory: 6.35 GB / 7.58 GB
    Container: Yes
    Shell: 4.2.46 - /bin/bash
  Binaries:
    Node: 10.23.1 - ~/.nvm/versions/node/v10.23.1/bin/node
    npm: 6.14.10 - ~/.nvm/versions/node/v10.23.1/bin/npm
  npmGlobalPackages:
    @aws-amplify/cli: 4.52.0
    cdk: 1.83.0
    coffeescript: 2.5.1
    esformatter: 0.11.3
    js-beautify: 1.13.2
    npm: 6.14.10
    prettier: 2.2.1
    typescript: 3.7.5

Describe the bug

We would like the following query to return "abc", "Abc" or "ABC" but it does not seem possible to perform a case insensitive query with DataStore.

DataStore.query(Post, "ABC");

Expected behavior

Query can perform case sensitive or insensitive searches. Insensitive search is more important for our use case.

Reproduction steps

Try to search case insensitive

Code Snippet

// Put your code below this line.

Log output

// Put your logs below this line

aws-exports.js

No response

Manual configuration

No response

Additional configuration

No response

Mobile Device

No response

Mobile Operating System

No response

Mobile Browser

No response

Mobile Browser Version

No response

Additional information and screenshots

No response

[chrisbonifacio]

Hey @sacrampton just wanted to give an update here. I spoke with @iartemiev and while DataStore does not support case insensitive search at the moment, it is something we'd like to add. I'm going to label this as a feature request for that reason until we implement it.

[sacrampton]

Hi @chrisbonifacio @iartemiev - it is a really critical requirement for my application so I hope it is a near term thing.

I have people speaking into phones to construct search strings - so case is whatever the device throws at you. I then need that to search the database. For example, user might say "pump" - speech to text will put "Pump" - and its in the database as "PUMP". Its really very important.

[iartemiev]

@sacrampton this is something we'll be able to accomplish efficiently with the SQLite adapter since we can utilize the DB engine for performing a case-insensitive query.
However, with IDB and AsyncStorage, there are no DB optimizations we can leverage, so we would need to retrieve all the items for a model into memory, then iterate over each record, calling e.g., toLowerCase() on the field in question and comparing that to the toLowerCase() of the operand, which will be fairly slow on large tables.

[sacrampton]

Sounds great @iartemiev - is this something that is possible to include with the new SQLite Adapter that we are going to be using as soon as available?

[iartemiev]

@sacrampton when using the contains operand (e.g., DataStore.query(Post, c => c.someField("contains", "ABC")) it will be case-insensitive by default with SQLite. We're about to publish an updated version with that adapter. I'll update in the other thread once it's released.

[sacrampton]

Hi @iartemiev - that works for my use case - looking forward to it.

[maxfadeev]

It’s been a while since the last message in this thread. I just wanted to ask if you have any updates on this. I would love if we could have case insensitive queries with SQLite.

[vrajasekhar1]

Hi, any update on this issue? Is there a way I can do case insensitive search using DataStore.query?

[espiegelberg]

Any update?

[danrivett]

We just had a user feedback meeting with our business users and they reported one of their biggest pain points is that they get incomplete search results when searching our DataStore-powered dashboard.

It turned out it was just because of case-sensitivity, and I said that would be a simple thing to fix.

Apparently though it's not a simple thing for us to fix because the underlying DataStore predicates don't support case insensitive matching.

Is there any update on this ticket?

This really seems like a fundamental search option, and so I'd love to get an update here.

Recently we've run into a number of different DataStore issues and I'm concerned this library may not be getting the level of support that's needed to be able to continue its production use.

I've long advocated for using DataStore over direct GraphQL queries and mutations because of the undifferentiated sync capabilities it provides. However I'm worried that the accumulation of issues like this and the timeframes for them being resolved (or not) is pushing us away, not encouraging us to continue.