Mongo – Querying documents within documents

Some of our collections in Mongo are built with embedded documents, This means the actual raw data is held in the following heirarchy:

OurMongoDB (DB)
OurMongoCollection (Collection)
                                _id          (document)
                                Car         (document)
                                                _id                       (embedded document)
                                                Channel             (embedded document)
                                                Make                   (embedded document)
                                                Model                  (embedded document)
                                                …
                                userId   (document)

This means that normal mongo db find statements will not work if you are searching based on criteria…

To search for embedded document data – use dot notation (http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29

--return only where channel is Test1
db.ourMongoCollection.find({"car.channel" : "Test1"});

--return only where channel is Test2
db.ourMongoCollection.find({"car.channel" : "Test2"});

--return count of all documents with channel as Test2
db.ourMongoCollection.find({"car.channel" : "Test2"}).count();

--return count of all channel Test1 documents with a make of Honda 
--(search criteria is case sensitive)
db.ourMongoCollection.find({"car.channel" : "Test1", "car.make" : "HONDA"}).count();
Advertisements

Querying Oracle with Case Insensitivity

Before 10g R2, the only way to do case insensitive querying was by using the LOWER or UPPER functions, NLS_UPPER or NLS_SORT or NLS_COMP session parameters to put the data in a state in which it could be compared.

For example, take a scenario where a table has 100 entries, 60 have the category ‘cars’ and 40 have the category ‘CARS’

select Category, count(*) from SCHEMA.CATEGORY_TABLE
Group by Category;

CATEGORY    COUNT(*)

cars                  60

CARS               40  

To be able to return all the data, as one category the lower or upper function can be used:

select count(*) from SCHEMA.CATEGORY_TABLE
WHERE lower(category)='caRs';

the count returned in 100

To then speed up the data retrieval, a function based index can be added on the table (If the retrieval cost outweights the computation cost of the index:

CREATE INDEX SCHEMA.I_LOWER_CATEGORY ON SCHEMA.CATEGORY_TABLE
(LOWER("CATEGORY"));

Since 10g R2, the case insensitivity can be set at session level:

This is handled by the NLS_COMP session parameter. The sole purpose of this variable is to determine how the predicates in SQL statements will evaluate to each other when a comparison is required. The typical comparison operator is the WHERE clause but also includes such comparisons as ORDER BY, START WITH, HAVING, etc.

SQL> alter session set NLS_COMP=ANSI;

Session altered.

SQL> alter session set NLS_SORT=BINARY_CI;

Session altered.

SQL> select count(*) from schema.table where category = ‘CaRs’;

  COUNT(*)

———-

       100

this is discussed in full detail here