Need help with case insensitive find

I have some cloud code that is trying to do a case insensitive find and it’s not working. I’m not sure where to start digging in so any pointers would be appreciated. Here are the details:

// cloud code 
    const query = new Parse.Query('Artist');
    query.equalTo('ens','RAc.eth');
// also tried hinting to use index but still no results
// query.hint('ens_unique1');
    query.explain();
    const result = await query.find({caseInsensitive:true}); 
// above returns no results

// mongosh query works: db.Artist.find({ens:'RAc.eth'}).collation( { locale: 'en', strength: 2 } );

The class Artist has an index on the property ‘ens’ that specifies collation:

{
    v: 2,
    key: { ens: 1 },
    name: 'ens_unique1',
    unique: true,
    partialFilterExpression: { ens: { '$exists': true } },
    collation: {
      locale: 'en_US',
      caseLevel: false,
      caseFirst: 'off',
      strength: 2,
      numericOrdering: false,
      alternate: 'non-ignorable',
      maxVariable: 'punct',
      normalization: false,
      backwards: false,
      version: '57.1'
    }

Here’s the output of explain:

verbose: RESPONSE from [GET] /parse/classes/Artist: {
  "response": {
    "results": {
      "explainVersion": "1",
      "queryPlanner": {
        "namespace": "crush.Artist",
        "indexFilterSet": false,
        "parsedQuery": {
          "$and": [
            {
              "ens": {
                "$eq": "RAc.eth"
              }
            },
            {
              "_rperm": {
                "$in": [
                  null,
                  "*"
                ]
              }
            }
          ]
        },
        "queryHash": "240CEE43",
        "planCacheKey": "2E6DB2D8",
        "maxIndexedOrSolutionsReached": false,
        "maxIndexedAndSolutionsReached": false,
        "maxScansToExplodeReached": false,
        "winningPlan": {
          "stage": "LIMIT",
          "limitAmount": 100,
          "inputStage": {
            "stage": "COLLSCAN",
            "filter": {
              "$and": [
                {
                  "ens": {
                    "$eq": "RAc.eth"
                  }
                },
                {
                  "_rperm": {
                    "$in": [
                      null,
                      "*"
                    ]
                  }
                }
              ]
            },
            "direction": "forward"
          }
        },
        "rejectedPlans": []
      },
      "executionStats": {
        "executionSuccess": true,
        "nReturned": 0,
        "executionTimeMillis": 27,
        "totalKeysExamined": 0,
        "totalDocsExamined": 34472,
        "executionStages": {
          "stage": "LIMIT",
          "nReturned": 0,
          "executionTimeMillisEstimate": 2,
          "works": 34474,
          "advanced": 0,
          "needTime": 34473,
          "needYield": 0,
          "saveState": 34,
          "restoreState": 34,
          "isEOF": 1,
          "limitAmount": 100,
          "inputStage": {
            "stage": "COLLSCAN",
            "filter": {
              "$and": [
                {
                  "ens": {
                    "$eq": "RAc.eth"
                  }
                },
                {
                  "_rperm": {
                    "$in": [
                      null,
                      "*"
                    ]
                  }
                }
              ]
            },
            "nReturned": 0,
            "executionTimeMillisEstimate": 2,
            "works": 34474,
            "advanced": 0,
            "needTime": 34473,
            "needYield": 0,
            "saveState": 34,
            "restoreState": 34,
            "isEOF": 1,
            "direction": "forward",
            "docsExamined": 34472
          }
        },
        "allPlansExecution": []
      },
      "command": {
        "find": "Artist",
        "filter": {
          "ens": "RAc.eth",
          "_rperm": {
            "$in": [
              null,
              "*",
              "*"
            ]
          }
        },
        "projection": {},
        "limit": 100,
        "$db": "crush"
      },
      "serverInfo": {
        "host": "clt-mbp.local",
        "port": 27017,
        "version": "6.0.1",
        "gitVersion": "32f0f9c88dc44a2c8073a5bd47cf779d4bfdee6b"
      },
      "serverParameters": {
        "internalQueryFacetBufferSizeBytes": 104857600,
        "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
        "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
        "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
        "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
        "internalQueryProhibitBlockingMergeOnMongoS": 0,
        "internalQueryMaxAddToSetBytes": 104857600,
        "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
      },
      "ok": 1
    }

Could it simply be a permission issue? Maybe try adding the option useMasterKey: true.

I tried adding the master key option and still does not work. Thanks for the suggestion! I’ll keep digging around and if I find out anything I’ll update the thread.

If you’re still having issues, maybe try an aggregate query to see if that returns what mongo does. I say this because aggregate doesn’t return parse objects, so it will at least help to determine behaviour?

https://docs.parseplatform.org/js/guide/#aggregate

@clt If you can’t figure it out, you may as well open an Parse Server issue, as it may be a bug. There should be CI tests in the /spec directory of Parse Server, where you may find examples for the caseInsensitive parameter. Maybe you can find a hint there, otherwise please open an issue and if you could also a PR with a failing test that demos the issue that would be great.

Thanks for replying! I finally got some time to poke at this and here’s what I found out.

tl;dr
I was mistaken and thought that caseInsensitive is a valid option for ParseQuery.find. It is not supported. So for now, I’m just accessing mongo directly to run my query. I’ll log an issue later. I think it could be made to work without too much effort.

I’m running parse-server 5.3.3 and parse 3.5.0

Details:
I debugged my server instance and found that ParseQuery.find does not recognize caseInsensitive as an option:

find(options
  /*:: ?: FullOptions*/
  )
  /*: Promise<Array<ParseObject>>*/
  {
    options = options || {};
    const findOptions = {};

    if (options.hasOwnProperty('useMasterKey')) {
      findOptions.useMasterKey = options.useMasterKey;
    }

    if (options.hasOwnProperty('sessionToken')) {
      findOptions.sessionToken = options.sessionToken;
    }

    if (options.hasOwnProperty('context') && typeof options.context === 'object') {
      findOptions.context = options.context;
    }
...

I noticed that DatabaseController.find does have caseInsensitive in it’s signature:

find(className, query, {
    skip,
    limit,
    acl,
    sort = {},
    count,
    keys,
    op,
    distinct,
    pipeline,
    readPreference,
    hint,
    caseInsensitive = false,
    explain
  } = {}, auth = {}, validSchemaController) {
...

So I thought a quick fix would be to modify ParseQuery.find to handle caseInsensitive and pass it along. I did that but it seems that the option gets dropped somewhere in the chain of calls before it gets to DatabaseController.find. I’ll circle back to this when I have some more time.

I had to use the DatabaseController to use this. I think Parse.Query still doesn’t support this:

    const databaseController = Parse.Server.database
    const result = await databaseController.find(
        '_User', 
        // constraints
        { username: username }, 
        // options"
        { caseInsensitive: true }
    )

It’d be nice to add support to Query API