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 (

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

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

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

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

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;


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:


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’;




this is discussed in full detail here