Case insensitive in an aggregate / lookup query with external variable

Working with Data
#1

Hi everyone, I’m struggling with a query lookup with two collections where I’m comparing strings of owners. Both the fields are on the two collections are named “owner”.

I’d need to match the collections based on an exadecimal value of these owner fields, type String, that is written often in a different way.

Let’s imagine 0xfba… in the first collection and on the other one 0xFBa…

If they are identical the query works fine, if not, it fails and is unable to retrieve data from the second collection.

I’m not figuring out the right syntax to use for a case insensitive $eq.

I’m using a backend service, and I’m trying to pass an external variable to the query, the owner field needs to match that external value, for both the collections.

This is part of the query

const ownerAddress = String(resultCardOwner.get("owner"));

      const pipeline = [
          {
              match: {
                $expr: {
                     $and: [
                        { $eq: ["$owner", ownerAddress] },
                        { $eq: ["$tokenId", cardId] },

                     ],
                },
              },
          },
          {
            lookup: {
              from: "CardOwners",
              localField: "owner",
              foreignField: "owner",
              as: "cardOwner",
            },
          },
          {
            project: { 
              tokenId:1,
              name:1,
              image:1,
              owner: 1,
              cardType:1,
              
              "cardOwner.name":1,
              "cardOwner.email": 1,
              "cardOwner.avatar": 1, 

            }
          }
      ];
      
      const result = query.aggregate(pipeline);
      return result;

I’d like to bring all the strings of owner for both the collections (Cards and CardOwners) to be case insensitive

Here

{ $eq: ["$owner", ownerAddress] },
{ $eq: ["$tokenId", cardId] },

and here ( I think)

  localField: "owner",
   foreignField: "owner",

Any suggestion? Thank you

#3

Hello @Gerry ,

Welcome to the community!! :wave:

In mentioned scenario for case insensitive search, you can use $regex with option “i”.

From a developer’s prespective, If you frequently run case-insensitive regex queries (utilizing the i option), you should create a case-insensitive index to support your queries. You can specify a collation on an index to define language-specific rules for string comparison, such as rules for lettercase and accent marks. A case-insensitive index greatly improves performance for case-insensitive queries. Here is an example on how to achieve this.

Regards,
Tarun

2 Likes