Mongo Query and SQL query Side by Side - Part 2

Continuing further from earlier post "Mongo Query and SQL query Side by Side", we will further explore some more mongo clauses.

 How to escape hypenated collection name in mongo shell:
      Our collection 'order-details' contains hypen in between when we try below query it does not work
       
        db.order-details.find().limit(1).pretty()

    so we need to use other syntax for quering this collection below both works well

        db['order-details'].find().pretty()   
                        or
        db.getCollection('order-details').find({}).pretty() 

Counting records

    Select count(*) from ordercopy where ShipPostalCode=98124

    db.ordercopy.find({"ShipPostalCode":98124}).count()
    db.ordercopy.count({"ShipPostalCode":98124})


Distinct
    select distinct ShipPostalCode from ordercopy where ShipCountry="USA"

    db.ordercopy.distinct( "ShipPostalCode" ,{"ShipCountry":"USA"})
   
    Output:
            [
                87110,
                98124,
                82520,
                99508,
                97219,
                97201,
                83720,
                97827,
                99362,
                97403,
                98034,
                94117,
                59801
            ]
   
    Query Returned distinct ShipPostalcode values as an array.

Distinct on Multiple Columns:

SQL:select distinct ShipName,ShipCity from Orders

db.orders.aggregate(
[
    {
        "$group":{
            "_id":{ShipName:"$ShipName",ShipCity:"$ShipCity"}
            }
    }
])
Output:
{ "_id" : { "ShipName" : "France restauration", "ShipCity" : "rue Royale" } }
{ "_id" : { "ShipName" : "Wilman Kala", "ShipCity" : "Helsinki" } }
{ "_id" : { "ShipName" : "Let's Stop N Shop", "ShipCity" : "San Francisco" } }
{ "_id" : { "ShipName" : "Trail's Head Gourmet Provisioners", "ShipCity" : "Kirkland" } }
{ "_id" : { "ShipName" : "Great Lakes Food Market", "ShipCity" : "Eugene" } }
{ "_id" : { "ShipName" : "Alfreds Futterkiste", "ShipCity" : "Berlin" } }
{ "_id" : { "ShipName" : "Océano Atlántico Ltda.", "ShipCity" : "Buenos Aires" } }
{ "_id" : { "ShipName" : "Alfred's Futterkiste", "ShipCity" : "Berlin" } }
{ "_id" : { "ShipName" : "LINO-Delicateses", "ShipCity" : "I. de Margarita" } }
{ "_id" : { "ShipName" : "La corne d'abondance", "ShipCity" : "avenue de l'Europe" } }
{ "_id" : { "ShipName" : "Consolidated Holdings", "ShipCity" : "London" } }
{ "_id" : { "ShipName" : "Bottom-Dollar Markets", "ShipCity" : "Tsawassen" } }
{ "_id" : { "ShipName" : "Wolski Zajazd", "ShipCity" : "Warszawa" } }
{ "_id" : { "ShipName" : "Folies gourmandes", "ShipCity" : "chaussée de Tournai" } }
{ "_id" : { "ShipName" : "Hungry Coyote Import Store", "ShipCity" : "Elgin" } }
{ "_id" : { "ShipName" : "Queen Cozinha", "ShipCity" : 891 } }
{ "_id" : { "ShipName" : "Vaffeljernet", "ShipCity" : "Århus" } }
{ "_id" : { "ShipName" : "Laughing Bacchus Wine Cellars", "ShipCity" : "Vancouver" } }
{ "_id" : { "ShipName" : "Around the Horn", "ShipCity" : "Colchester" } }
{ "_id" : { "ShipName" : "Piccolo und mehr", "ShipCity" : "Salzburg" } }


Group by Count:

        a]  select ShipPostalCode,count(*) from ordercopy group by ShipPostalCode
            db.ordercopy.aggregate([
                {"$group" : {_id:"$ShipPostalCode", count:{$sum:1}}}
            ])   

        b]
            select ShipPostalCode,ShipCountry,count(*) from ordercopy group by ShipPostalCode,ShipCountry

            db.ordercopy.aggregate(
                {
                    "$group" : 
                        {
                            _id : {ShipPostalCode:"$ShipPostalCode", ShipCountry: "$ShipCountry"},
                            count : { $sum : 1}
                        }
                }
            )
        c]
        select ShipPostalCode,ShipCountry,count(*) from ordercopy group by ShipPostalCode,ShipCountry order by count(*)

        db.ordercopy.aggregate(
                {
                    "$group" : 
                        {
                            _id : {ShipPostalCode:"$ShipPostalCode", ShipCountry: "$ShipCountry"},
                            count : { $sum : 1}
                        }
                },
                {
                    $sort:{"count":-1}
                }
            )

        d]
        select ShipPostalCode,ShipCountry,count(*) from ordercopy group by ShipPostalCode,ShipCountry order by ShipCountry desc,ShipPostalCode desc
        db.ordercopy.aggregate(
                {
                    "$group" : 
                        {
                            _id : {ShipPostalCode:"$ShipPostalCode", ShipCountry: "$ShipCountry"},
                            count : { $sum : 1}
                        }
                },
                {
                    $sort:{"_id.ShipCountry":-1,"_id.ShipPostalCode":-1}
                }
            )
f) group by sum of column or computed column

    db.getCollection('order-details').aggregate(
        {
            "$group" : 
                {
                    _id : {OrderID:"$OrderID"},
                    totalAmount: { $sum: { $multiply: [ "$UnitPrice", "$Quantity" ] } },
                }
        },
        {
            $sort:{"_id.OrderID":1}
        }
    )
g) in clause

    select * from ordercopy where ShipPostalCode in (8010,98124)

    db.ordercopy.find({
        ShipPostalCode :{ $in : [8010,98124]}
    })

h) Not equal to
    select * from ordercopy where ShipCountry != 'Austria'

    db.ordercopy.find({"ShipCountry": {$ne: "Austria"}})

i) Not In
    select * from ordercopy where ShipCountry not in ('Austria','Finland')

    db.ordercopy.find({
        ShipCountry :{ $nin : ['Austria','Finland']}
    })

j) $all clause

download https://github.com/ozlerhakan/mongodb-json-files/blob/master/datasets/palbum.zip
extract zip & import it to palbum database

mongoimport --db palbum --collection albums --drop --file  /path_to_palbum_dir/albums.json
mongoimport --db palbum --collection images --drop --file /path_to_palbum_dir/images.json


a) below query select all records where tags array contain both 'sunrises' & 'travel' and may be other tag extra with this two
db.images.find( { tags: { $all: [ "sunrises", "travel"] } } )

Output:
    { "_id" : 1, "height" : 480, "width" : 640, "tags" : [ "cats", "sunrises", "kittens", "travel", "vacation", "work" ] }
    { "_id" : 4, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "kittens", "travel" ] }
    { "_id" : 8, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "kittens", "travel" ] }
    { "_id" : 9, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "travel" ] }
    { "_id" : 21, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "travel", "vacation" ] }
    { "_id" : 19, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "kittens", "travel", "work" ] }
    { "_id" : 22, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel", "vacation", "work" ] }
    { "_id" : 24, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "kittens", "travel", "vacation" ] }
    { "_id" : 26, "height" : 480, "width" : 640, "tags" : [ "sunrises", "kittens", "travel", "vacation" ] }
    { "_id" : 28, "height" : 480, "width" : 640, "tags" : [ "sunrises", "kittens", "travel" ] }
    { "_id" : 32, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "kittens", "travel", "vacation", "work" ] }
    { "_id" : 27, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "kittens", "travel", "work" ] }
    { "_id" : 43, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "kittens", "travel", "vacation" ] }
    { "_id" : 45, "height" : 480, "width" : 640, "tags" : [ "cats", "sunrises", "travel", "vacation", "work" ] }
    { "_id" : 51, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel", "vacation", "work" ] }
    { "_id" : 52, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "sunrises", "kittens", "travel", "work" ] }
    { "_id" : 57, "height" : 480, "width" : 640, "tags" : [ "cats", "sunrises", "travel", "work" ] }
    { "_id" : 58, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel", "vacation", "work" ] }
    { "_id" : 59, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "kittens", "travel" ] }
    { "_id" : 71, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }


b) below query select all records where tags array contain both 'sunrises' & 'travel' but nothing extra tag with it
db.images.find( { tags: { $all: [ [ "sunrises", "travel" ] ] } } )

Output:
    { "_id" : 71, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 89, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 463, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 548, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 557, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1065, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1095, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1145, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1584, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1619, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 1701, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2052, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2249, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2256, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2275, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2664, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2817, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 2969, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 3220, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }
    { "_id" : 3320, "height" : 480, "width" : 640, "tags" : [ "sunrises", "travel" ] }


How to Drop database in mongo
 suppose you want to drop database named 'grades'  then to drop it
     first switch to 'grades' & then issue 'dropDatabase'

     use grades
     db.dropDatabase()


$size clause in mongodb
    Below query will select all records where 'tags' array has length 3'

    db.images.find( { tags: { $size: 3 } } )

    Output:

        { "_id" : 2, "height" : 480, "width" : 640, "tags" : [ "dogs", "kittens", "work" ] }
        { "_id" : 12, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "travel" ] }
        { "_id" : 14, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "vacation" ] }
        { "_id" : 17, "height" : 480, "width" : 640, "tags" : [ "kittens", "vacation", "work" ] }
        { "_id" : 9, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "travel" ] }
        { "_id" : 23, "height" : 480, "width" : 640, "tags" : [ "cats", "kittens", "travel" ] }
        { "_id" : 29, "height" : 480, "width" : 640, "tags" : [ "kittens", "vacation", "work" ] }
        { "_id" : 30, "height" : 480, "width" : 640, "tags" : [ "cats", "sunrises", "vacation" ] }
        { "_id" : 28, "height" : 480, "width" : 640, "tags" : [ "sunrises", "kittens", "travel" ] }
        { "_id" : 33, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "kittens" ] }
        { "_id" : 36, "height" : 480, "width" : 640, "tags" : [ "dogs", "kittens", "vacation" ] }
        { "_id" : 38, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "work" ] }
        { "_id" : 47, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "travel" ] }
        { "_id" : 50, "height" : 480, "width" : 640, "tags" : [ "sunrises", "vacation", "work" ] }
        { "_id" : 49, "height" : 480, "width" : 640, "tags" : [ "dogs", "cats", "travel" ] }
        { "_id" : 53, "height" : 480, "width" : 640, "tags" : [ "sunrises", "vacation", "work" ] }
        { "_id" : 56, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "vacation" ] }
        { "_id" : 48, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "work" ] }
        { "_id" : 61, "height" : 480, "width" : 640, "tags" : [ "kittens", "travel", "vacation" ] }
        { "_id" : 60, "height" : 480, "width" : 640, "tags" : [ "dogs", "sunrises", "work" ] }

    Each entry has 3 elements in tags  array.