Mongo Query and SQL query Side by Side - Part 1

Here we will try to write simple SQL queries & parallel Mongo Query.

First we need test database for that Download Northwind Dump:
    https://github.com/tmcnab/northwind-mongo/archive/master.zip

    extract and run shell script within.It will create Database called 'Northwind' in mongodb.

        bash mongo-import.sh

connect to mongo shell & switch to Northwind database.

>mongo
>use Northwind

check imported collections

>show collections;
Output:
    categories
    customers
    employee-territories
    northwind
    order-details
    orders
    products
    regions
    shippers
    suppliers
    territories


Queries:

1) Select all record but only few columns

    select CategoryID,CategoryName,field6 from categories

    db.categories.find({},{CategoryID:1,CategoryName:1,field6:1}).pretty();  

2) where clause
  a]
    select CategoryID,CategoryName from categories where CategoryID > 5

    db.categories.find({CategoryID: {$gt:5} },{CategoryID:1,CategoryName:1}).pretty();

  b]
  select CategoryID,CategoryName from categories where CategoryID > 5 and CategoryID < 8

  db.categories.find({$and:[{CategoryID: {$gt:5}},{CategoryID: {$lt:8}}]},{CategoryID:1,CategoryName:1}).pretty()

  c]
    select CategoryID,CategoryName from categories where CategoryID > 5 and CategoryID <= 8

    db.categories.find({$and:[{CategoryID: {$gt:5}},{CategoryID: {$lte:8}}]},{CategoryID:1,CategoryName:1}).pretty()

 

3) Like Clause
  a]  select CategoryID,CategoryName from categories where CategoryID > 5 and CategoryID <= 8 and CategoryName like '%ea%'
 
   db.categories.find({$and:[
        {CategoryID: {$gt:5}},
        {CategoryID: {$lte:8}},
        {CategoryName: /ea/ }    
      ]},{CategoryID:1,CategoryName:1}).pretty()  

  b]
    select CategoryID,CategoryName from categories where CategoryID > 5 and CategoryID <= 8 and CategoryName like 'Sea%'

    db.categories.find({$and:[
        {CategoryID: {$gt:5}},
        {CategoryID: {$lte:8}},
        {CategoryName: /^Sea/ }    
      ]},{CategoryID:1,CategoryName:1}).pretty()  

 c]
  select CategoryID,CategoryName from categories where CategoryID > 1 and CategoryID <= 8 and CategoryName like '%ts'

  db.categories.find({$and:[
        {CategoryID: {$gt:1}},
        {CategoryID: {$lte:8}},
        {CategoryName: /ts$/ }    
      ]},{CategoryID:1,CategoryName:1}).pretty()


3) Date Manipulation

a] get current date as computed column
        select OrderDate,now() from  orders

        db.orders.aggregate([{
        $project: {
            dt: new Date(),
            OrderDate:1
        }
        }])    

b] conversion of datetime string to iso datetime

    select
        OrderDate,
        to_char(TO_TIMESTAMP(OrderDate, 'YYYY-MM-DD HH24:MI:SS') at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as OrderDateISO
    from
        orders

    db.orders.aggregate( [ {
    $project: {
        OrderDateISO: {
            $dateFromString: {
                dateString: '$OrderDate',
                timezone: 'America/New_York'
            }
        },
        OrderDate:1
    }
    } ] )

    For mondodb version less than 3.6 , '$dateFromString' functionality is not supported.


Extracting Date Part:

   a) Substring
        db.orders.aggregate([{
            $project: {
                dateSubString: { $substr: [ "$OrderDate", 0, 10 ] },
                timeSubString: { $substr: [ "$OrderDate", 11, 8 ] }
            }
        }])      

    b) concatenation
 
    db.orders.aggregate(
        [
            {
                $project: {
                        OrderID:1,
                        ShipName :1,
                        ShipAddress : 1,
                        ShipCity : 1,
                        ShipRegion :1,
                        ShipPostalCode :1,
                        ShipCountry : 1,
                        ShipCountry : 1,
                        OrderDate:new ISODate()
                }
            }
        ]
    )

6) Making Copy of collection
    db.orders.aggregate([ { $match: {} }, { $out: "ordercopy" } ])  

    this will create new collection 'ordercopy' in same database which will hold same data as 'order'

7) Convert a string column to datetiem column

a]    Original structure of OrderCopy collection is like

    {
        "_id" : ObjectId("5a83be15f653cf290a966288"),
        "OrderID" : 10269,
        "CustomerID" : "WHITC",
        "EmployeeID" : 5,
        "OrderDate" : "1996-07-31 00:00:00.000",
        "RequiredDate" : "1996-08-14 00:00:00.000",
        "ShippedDate" : "1996-08-09 00:00:00.000",
        "ShipVia" : 1,
        "Freight" : 4.56,
        "ShipName" : "White Clover Markets",
        "ShipAddress" : "1029 - 12th Ave. S.",
        "ShipCity" : "Seattle",
        "ShipRegion" : "WA",
        "ShipPostalCode" : 98124,
        "ShipCountry" : "USA"
    }

    where OrderDate is string ,now we will convert this column to be of type date as follows

    var cursor = db.ordercopy.find();
    while (cursor.hasNext()) {
    var doc = cursor.next();
        db.ordercopy.update({_id : doc._id}, {$set : {OrderDate : new ISODate(doc.OrderDate) }});
    }

    After modification OrderCopy collection structure is as follows.

        {
            "_id" : ObjectId("5a83be15f653cf290a966288"),
            "OrderID" : 10269,
            "CustomerID" : "WHITC",
            "EmployeeID" : 5,
            "OrderDate" : ISODate("1996-07-31T00:00:00Z"),
            "RequiredDate" : "1996-08-14 00:00:00.000",
            "ShippedDate" : "1996-08-09 00:00:00.000",
            "ShipVia" : 1,
            "Freight" : 4.56,
            "ShipName" : "White Clover Markets",
            "ShipAddress" : "1029 - 12th Ave. S.",
            "ShipCity" : "Seattle",
            "ShipRegion" : "WA",
            "ShipPostalCode" : 98124,
            "ShipCountry" : "USA"
        }

b] Finding Record between two date

    select * from ordercopy where OrderDate between '1996-07-01' and '1996-08-01'
    
    db.ordercopy.find({
            OrderDate: {
                $gte: ISODate("1996-07-01T00:00:00Z"),
                $lt: ISODate("1996-08-01T00:00:00Z")
            }
        })

 8] Rename field
    Here we will rename field 'OrderDate' to 'order_date' in 'ordercopy' collection as follows

    db.ordercopy.update({}, {$rename: {"OrderDate": "order_date"}}, false, true);

    After Rename OrderCopy structure look like:

        {
            "_id" : ObjectId("5a83be15f653cf290a966288"),
            "OrderID" : 10269,
            "CustomerID" : "WHITC",
            "EmployeeID" : 5,
            "RequiredDate" : "1996-08-14 00:00:00.000",
            "ShippedDate" : "1996-08-09 00:00:00.000",
            "ShipVia" : 1,
            "Freight" : 4.56,
            "ShipName" : "White Clover Markets",
            "ShipAddress" : "1029 - 12th Ave. S.",
            "ShipCity" : "Seattle",
            "ShipRegion" : "WA",
            "ShipPostalCode" : 98124,
            "ShipCountry" : "USA",
            "order_date" : ISODate("1996-07-31T00:00:00Z")
        }