Query Sitecore xDB MongoDatabases Part 2

Let’s query Mongo Database and fire some queries on collections to see what all Sitecore stores in the collections. We will also try to learn the syntax of mongo queries something similar that we use to do in our academic’s days trying to remember SQL queries ascreate table tablenameORselect * from tablename where fieldname=value orderby fieldname.This post is a continuation of my previous blog post Query Sitecore xDB MongoDatabases Part 1 where I shared information about how to set up RoboMongo so that it can understand GUID. Here I would be sharing few queries that can be executed from mongo shell but nothing about executing queries from RoboMongo, well this allows me to write a 3rd post in the series where I will show how to query mongo db via RoboMongo.A point worth noting is Mongo is case sensitive so Find() and find() are different and the former will throw an error saying it is not a function.Of course, the mongo shell needs to be running from where one can query mongo databases. So how do I run mongo shell?

1. Open command prompt and change the directory to /mongo/bin
2. Execute “mongo” and you will be connected to test db.
3. Execute “use admin”. Mongo shell will switch database to admin and display message “switched to db admin”. “admin” is the database where all information related to security is stored.
4. Execute db.auth(“username”,”password”) command. Once successfully authenticated mongo shell will display “1”
5. Now for connecting to the desired mongo db execute “use databasename” statement for e.g use sc82up1play_analytics

If authentication is not enabled on mongo instance you can skip #3 and #4 steps. Why would one leave a mongo instance vulnerable and be hacked so as a best practice authentication, should always be enabled and if you want to learn how to enable it follow below links,

• Sitecore xDB Enabling Authentication
• Friday Sitecore Best Practice: How to Secure MongoDB

Let’s start! We now have enough backgroundfind()To query data from MongoDB collection, you need to use MongoDB's find() method. It would list all the records from a collection and is equivalent to SELECT statement in SQL.Syntax of find() method is[code language="SQL"]db.COLLECTION_NAME.find()[/code]For listing all records from Contacts or Interactions collections of sitecore_analytics database you will fire up,

• db.Contacts.find()
• db.Interactions.find()

For getting a record to match a field value syntax would be[code language="SQL"]db.COLLECTION_NAME.find({“key:value”}) [/code]Or[code language="SQL"]db.COLLECTION_NAME.find({“key.key:value”})[/code]if the fields are nested we use dot notation to separate out top-level fields.Examplesdb.Contacts.find({"System.VisitCount":"5"}) where value of “VisitCount” is 5 and it falls under a top level field “System”db.Interactions.find({"_t":"VisitData"} ) is an example of top level field.and in MongoDBSyntax for and is,[code language="SQL"]db.collection.find({$and: [{key1: value1}, {key2:value2}]})[/code]Key refers to as fieldname and value is what you would have the record for.Exampledb.Contacts.find({$and:[{"System.VisitCount":5},{"System.Value":10}]})or in MongoDBSyntax for or is,[code language="SQL"]db.collection.find({$or: [{key1: value1}, {key2:value2}]})[/code]Exampledb.Contacts.find({$or:[{"System. Value ":5},{"System.Value":10}]})sort()Syntax for sort() is,[code language="SQL"]db.COLLECTION_NAME.find().sort({KEY:1})[/code]To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order. If the sorting preference is not specified, then sort() method will display the documents in ascending order. sort() is equivalent to OrderBy in SQL.Examplesdb.Contacts.find({}).sort({"System.Value":-1})db.Contacts.find({}).sort({"System.Value":1})If we want to find out the latest record in Interactions collection there are two fields “StartDateTime” and “SaveDateTime” which can be queried,db.Interactions.find().sort({"StartDateTime":-1}).pretty()db.Interactions.find().sort({"SaveDateTime":-1}).pretty()limit()As the name says limit() can be used to limit the number of records to be displayed on mongo shellSyntax for limit() is[code language="SQL"]db.COLLECTION_NAME.find().limit(NUMBER)[/code]sort() and limit() can be used to match TOP statement in SQL.Examplesdb.Contacts.find().limit(2)db.Contacts.find().sort({"System.Value":1}).limit(2)db.Contacts.find().sort({"System.Value":-1}).limit(2)db.Interactions.find().sort({"SavedDateTime":-1}).limit(1)Here‘s the end of the post and all I have to share on Mongo for now. I will soon be writing another post to talk on the above basic mongo queries in RoboMongo.