CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkphp-mongodb
Nida Amin
Nida Amin

Reputation: 755

Case insensitive search inside projection condition

The data in hotel table is stored like

   {
     _id: ....,
     HotelName: ...,
     MasterKeyDetails:[
     { 
       MasterKeyId: 36K1,
       ...
     },
     {
        MasterKeyId: 36J1,
       ...
     }
     ]

  }

I have written below query 

       $cursor = $this->collection->aggregate(array(
                array(
                    '$match' => array(
                        "_id" => new MongoDB\BSON\ObjectID($this->id)
                    )
                ),
                array(
                    '$project' => array(
                        'MasterKeyDetails' => array(
                            '$filter' => array(
                                'input' => '$MasterKeyDetails',
                                'as' => 'room',
                                'cond' => array(
                                    '$eq' => array('$$room.MasterKeyId', $this->MasterKeyId)
                                )
                            )
                        ),
                    )
                )
            )
         )->toArray();

It is searching fine. It searches when $this->MasterKeyId contains 36K1, but does not search when $this->MasterKeyId contains 36k1. I want it should fetch despite of case sensitive data...

Please help!!!

Upvotes: 0

Views: 228

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

You can use $toUpper to normalise the data:

   $cursor = $this->collection->aggregate(array(
            array(
                '$match' => array(
                    "_id" => new MongoDB\BSON\ObjectID($this->id)
                )
            ),
            array(
                '$project' => array(
                    'MasterKeyDetails' => array(
                        '$filter' => array(
                            'input' => '$MasterKeyDetails',
                            'as' => 'room',
                            'cond' => array(
                                '$eq' => array(array('$toUpper' =>'$$room.MasterKeyId'), $this->MasterKeyId)
                            )
                        )
                    ),
                )
            )
        )
     )->toArray();

In the same way you make sure the data you have to supply for comparison is also in the same case. There is also $toLower if you prefer that way.

As yet there is no other "case insensitive match" function you can use as part of an aggregation pipeline, so "normalizing" is the current approach.

Upvotes: 1

Related Questions