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")
        }

How to emit socket.io event from express route

To demonstrate emitting socket.io event inside express route ,
we will  create a chat application first & them from our express route we will emit clear event to all chats on client.

lets start with creating express.js app.

first install express generator

npm install express-generator -g

then

mkdir xpress002
cd xpress002

we will use express generator to create boilerplate code as

    express -e

 now install socket.io  package  as
    npm install socket.io --save

Creating client  involve creating view & javascript to connect to socket.io from client side & displaying it on html also we need to serve our view through route.

a) inside views create socketclient.ejs with following content

            <html>

            <head>
                <title>Real time web chat</title>
                <script src="/socket.io/socket.io.js"> </script>
                <script src="/static/javascripts/code.js"></script>
            </head>

            <body>

                Name:
                <input type="text" name="name" id="name" style='width:350px;' /><br/>
                Message:
                <input type="text" name="field" id="field" style='width:350px;' /><br/>

                <input type="button" name="send" id="send" value="send" />
                <div id="content" style='width: 500px; height: 300px; margin: 0 0 20px 0; border: solid 1px #999; overflow-y: scroll;'>

                </div>
                <input type="button" name="clear" id="clear" value="clear" />
            </body>

            </html>

b) inside public/javascripts create code.js as follows

    window.onload = function () {

        var messages = [];
        var socket = io.connect('http://localhost:3000');

        var field = document.getElementById("field");
        var sendButton = document.getElementById("send");
        var content = document.getElementById("content");
        var name = document.getElementById("name");
        var clearButton = document.getElementById("clear");

        socket.on('warmup', function (data) {
            messages = [];
            if (data.message) {
                messages.push(data);
                var html = '';
                for (var i = 0; i < messages.length; i++) {
                    html += '<b>' + (messages[i].username ? messages[i].username : 'Server') + ': </b>';
                    html += messages[i].message + '<br />';
                }
                content.innerHTML = html;
            } else {
                console.log("There is a problem:", data);
            }
        });

        socket.on('cleanup', function (data) {
            messages = [];
            content.innerHTML = '';     
        });

        socket.on('message', function (data) {
            if (data.message) {
                messages.push(data);
                var html = '';
                for (var i = 0; i < messages.length; i++) {
                    html += '<b>' + (messages[i].username ? messages[i].username : 'Server') + ': </b>';
                    html += messages[i].message + '<br />';
                }
                content.innerHTML = html;
            } else {
                console.log("There is a problem:", data);
            }
        });

        sendButton.onclick = function () {
            if (name.value == "") {
                alert("Please type your name!");
            } else {
                var text = field.value;
                socket.emit('send', { message: text, username: name.value });
            }
        };

        clearButton.onclick = function () {
            socket.emit('cleanup', {});
        };

    }

c) inside bin/www file just below
        var server = http.createServer(app);
   add

        var io = require('socket.io').listen(server);
        app.io = io;//to let io accessible in router object
        io.on('connection', function (socket) {
        socket.emit('warmup', { message: 'welcome to the chat using socket.io' });

        socket.on('send', function (data) {
        io.sockets.emit('message', data);
        });

        socket.on('cleanup', function () {
        io.sockets.emit('cleanup', '');
        });
        });

d)  inside routes  add socket.js with following content

        var express = require('express');
        var router = express.Router();
        var path = require('path');

        router.get('/foo', function (req, res) {
            req.app.io.sockets.emit('cleanup', {});
            res.end()
        });

        router.get('/socketclient', function (req, res) {
        res.render('socketclient')
        })

        module.exports = router;

e) inside  app.js in root folder of application of express app make sure static point to public folder as
        app.use('/static', express.static(path.join(__dirname, 'public')));

        and route defined for socket included as  

        var socket = require('./routes/socket');
        app.use('/socket', socket);

my app.js file looks like

        var express = require('express');
        var path = require('path');
        var favicon = require('serve-favicon');
        var logger = require('morgan');
        var cookieParser = require('cookie-parser');
        var bodyParser = require('body-parser');

        var index = require('./routes/index');
        var users = require('./routes/users');
        var socket = require('./routes/socket');


        var app = express();

        // view engine setup
        app.set('views', path.join(__dirname, 'views'));
        app.set('view engine', 'ejs');

        // uncomment after placing your favicon in /public
        //app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
        app.use(logger('dev'));
        app.use(bodyParser.json());
        app.use(bodyParser.urlencoded({ extended: false }));
        app.use(cookieParser());
        app.use('/static', express.static(path.join(__dirname, 'public')));

        app.use('/', index);
        app.use('/users', users);
        app.use('/socket', socket);


        // catch 404 and forward to error handler
        app.use(function (req, res, next) {
        var err = new Error('Not Found');
        err.status = 404;
        next(err);
        });

        // error handler
        app.use(function (err, req, res, next) {
        // set locals, only providing error in development
        res.locals.message = err.message;
        res.locals.error = req.app.get('env') === 'development' ? err : {};

        // render the error page
        res.status(err.status || 500);
        res.render('error');
        });

        module.exports = app;

Run express app with

        DEBUG=xpres002:* npm start


Now to test socket.io chat application open below url in two tabs

    http://localhost:3000/socket/socketclient

    add some stuff inside name & message textbox and click 'send'.Message is delivered to both window,try it few times.now in third tab hit

    http://localhost:3000/socket/foo

    you will see both chat client message history is cleared.