Continuing further from earlier post "Mongo Query and SQL query Side by Side"
at
msdotnetbuddy.blogspot.com/2018/02/mongo-query-and-sql-query-side-by-side.html,
we will further explore some more mongo clauses.
We will quickly see which data we are going to use here for this article
though its same as part 1,
wget wget https://github.com/tmcnab/northwind-mongo/archive/master.zip
this will download master.zip in current folder
unzip this folder as
unzip master.zip -d master
import the data
login into extracted folder till you reached "northwind-mongo-master"
folder then
run
bash mongo-import.sh
Now if you have enabled username & password authentication then this import
will not work.
login to shell
mongosh --port 27017 -u "sangram" -p
"sangram#81" --authenticationDatabase "admin"
to create Northwind database run following query.
use Northwind;
Now switch database to add access to your user to Northwind database.
use admin
db.grantRolesToUser(
"sangram",
[
{ role: "readWrite", db: "Northwind" }, // Read and write access
{ role: "dbAdmin", db: "Northwind" } // Database admin access
]
);
We need to update mongo-import.sh bash script,for that purpose run
nano mongo-import.sh
Find
mongoimport -d Northwind -c "$filename" --type csv --file "$f" --headerline
replace it with
mongoimport --authenticationDatabase admin --username sangram
--password sangram#81 -d Northwind -c "$filename" --type csv
--file "$f" --headerline
Save file & exit.
Now run shell script to import the mongo data.
bash mongo-import.sh
Here my username is "sangram" and password is "sangram#81"
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
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.
No comments:
Post a Comment