mongo shell aggregate framework examples

!!!!!!!!!!!!!!!!
Group
!!!!!!!!!!!!!!!!

db.products.aggregate([ {$group: {_id:"$category" , num_products : {$sum:1} }} ])
will return document "result" that have ids of categories that there is in products collection with field num_products with value of number of items of specified category

db.products.aggregate([ {$group: {_id: {"manufacturer" : "$manufacturer" , "category":"$category" } , num_products : {$ sum:1} }} ])
group by compound key example

db.products.aggregate([ {$group: {_id:"$manufacturer" , sum_prices: {$sum:"$price"} }} ])
calculation of sum of totall price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , avg_price: {$avg:"$price"} }} ])
calculation of the average price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , categories: {$push:"$category"} }} ])
getting collection groupped by manufacturer with array (set) of categories where every category as many time in array as many such category of such manufacturer is exists in collection

db.products.aggregate([ {$group: {_id:"$manufacturer" , categories: {$push:"$category"} }} ])
getting collection groupped by manufacturer with array (set) of categories where every category as many time in array as many such category of such manufacturer is exists in collection

db.zips.aggregate([{$group:{_id:"$state","pop": {$max:"$pop"}}}])
The aggregation query that will return the population of the postal code in each state with the highest population.

db.group.aggregate([{
$group:{_id:   { class_id: "$class_id" , student_id :  "$student_id" } , average : {"$avg"  : "$score"}}} ,
{$group: {_id : "$_id.class_id" , average :{ "$avg" : "$average"}}
}])
The two step group example, on the first step we creating collection with unique pairs class_id and student_id evaluate average score for this pairs, on the second step we getting calculatting average score of certain student for certain class

!!!!!!!!!!!!!!!!
Project
!!!!!!!!!!!!!!!!
db.zips.aggregate([{$project:{_id:0, city:{$toLower:"$city"}, pop:1, state:1, zip:"$_id"}}])

projection example:
from such collection:
{
"city" : "ACMAR",
"loc" : [
-86.51557,
33.584132
],
"pop" : 6055,
"state" : "AL",
"_id" : "35004"
}

will return such:
{
"city" : "acmar",
"pop" : 6055,
"state" : "AL",
"zip" : "35004"
}

db.products.aggregate([ {$group: {_id:"$manufacturer" , sum_prices: {$sum:"$price"} }} ])
calculation of sum of totall price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , avg_price: {$avg:"$price"} }} ])
calculation of the average price of devices of produced by every manufacturer


!!!!!!!!!!!!!!!!
Match
!!!!!!!!!!!!!!!!

db.zips.aggregate([{ $match : {state: NY} }])
kind of fillter, will return only documents where state is NY

!!!!!!!!!!!!!!!!!!!!!!!!!!!
Sort, Skip and Limit
!!!!!!!!!!!!!!!!!!!!!!!!!!!

db.zips.aggregate([{ $sort: {state : 1} }  , {$skip:10} , {$limit:5}]) 
query with just a sort stage to sort by state, ascending, skip the first 10 and get only next 5

!!!!!!!!!!!!!!!!
First and Last
!!!!!!!!!!!!!!!!

db.fun.aggregate([{$sort:{c:1}}, {$group:{_id:"$a", c:{$first:"$c"}}}])
will return collection of different "a" and for "c" key for the document from this collection will be the smallest "c" value for the corresponded "a"

!!!!!!!!!!!!!!!!
Unwind
!!!!!!!!!!!!!!!!

db.posts.aggregate([{ $unwind:"$comments"}]) 

from the posts collection will return new collection in which for every array element in comments arrat will be create new document

mongo shell indexes usage + miscellaneous comands

!!!!!!!!!!!!!!!!
Indexes
!!!!!!!!!!!!!!!!

db.students.ensureIndex({class:1, student_name:-1})
adding an index to a collection named students, having the index key be class, student_name.
class: ascending sorted index

student_name: descending sorted index

db.system.indexes.find()
getting all added indexes in the database

db.students.getIndexes()
getting all added indexes in the collection students

db.students.dropIndex({'class':1, 'student_name':-1})
dropping index created by db.students.ensureIndex({class:1, student_name:-1}) command

db.students.ensureIndex({''student_name':-1} , {unique:true})
create index on student_name that is unique (this action, actually, force student_name be unique)

db.students.ensureIndex({''student_name':-1} , {unique:true} , {dropDups:true})
create index on student_name that is unique and delete all duplicates of the same student_name

db.students.ensureIndex({''student_name':-1} , {unique:true} , {sparce:true})
create index on student_name that is unique and only on the documents that has student_name not null

db.scores.totalIndexSize()
getting size of al indexes in the collection scores

db.scores.find({type:"essay" , score:50} ).hint({type: 1})
if in the collection score 2 indexes were added (one on type and one on score), the hint command forces to use index on type

db.scores.find({type:"essay" , score:50}).hint({$natural:1})
forces not tu use index at all

!!!!!!!!!!!!!!!!
Miscellaneous
!!!!!!!!!!!!!!!!

db.scores.find({type:"essay" , score:50}).explain()
getting information how find was actually perfomed

db.scores.stats()
getting collection statistics

db.system.profile.find()
finding logs of the selected DB

db.students.drop()
dropping students collection

db.dropDatabase()
dropping database

mongoimport -d school -c students < students.json
import documents from students.json to the school database to the students collection

hw 3.2 jUnit coverage

//here the example of coverage hw 3.2 + 3.3 by unit tests

import com.mongodb.*;
import course.BlogPostDAO;
import org.junit.After;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import java.net.UnknownHostException;
import java.util.Arrays;
import java.util.List;
import static junit.framework.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

public class BlogPostDAOTest {

    static MongoClient mongoClient;
    static DB blogDatabase;
    static DBCollection blogPostCollection;
    static BlogPostDAO blogPostDAO;

    // this method called before all test (once), to initialize DB variables
    @BeforeClass
    public static void setUp() throws UnknownHostException {
        mongoClient = new MongoClient();
        blogDatabase = mongoClient.getDB("blog");
        blogPostCollection = blogDatabase.getCollection("posts");
        blogPostDAO = new BlogPostDAO(blogDatabase);
        blogPostCollection.drop();
    }

    //this method calls after every test to clean usersCollection
    @After
    public void dropCollection() {
        blogPostCollection.drop();
    }

    @Test
    public void addUserTest() {

        System.out.println("Starting test on adding post");
        String body = "this is body of post";

        blogPostDAO.addPost("hello post", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");

        DBObject find = new BasicDBObject("body", "this is body of post");

        DBObject post = blogPostCollection.find(find).next();

        assertTrue(post.get("body").equals(body));

        System.out.println("Post successfully added");
        System.out.println("");

    }

    @Test
    public void findPostByPermalinkTest() {
        System.out.println("Starting test on finding post by permalink");

        String body = "this is body of post";

        String permalink = blogPostDAO
                .addPost("hello post", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");
        DBObject foundPost = blogPostCollection.findOne();

        assertEquals(foundPost, blogPostDAO.findByPermalink(permalink));

        System.out.println("Test on finding post by permalink successfully finished");
        System.out.println("");

    }

    @Test
    public void addCommentTest() {
        System.out.println("Starting test on adding comment with email");

        String body = "this is body of post";

        String permalink = blogPostDAO.
                addPost("Post number: ", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");

        String theSecondCommentName = "The second comment";
        String theFirstCommentBody = "Comment 1 body";


        blogPostDAO.addPostComment("The first comment", "b1102@pochta.ru", theFirstCommentBody, permalink);
        blogPostDAO.addPostComment(theSecondCommentName, null, "Comment 2 body", permalink);


        DBObject commentedPost = blogPostDAO.findByPermalink(permalink);

        List<BasicDBObject> comments = (List<BasicDBObject>) commentedPost.get("comments");

        assertEquals(comments.size(), 2);

        Assert.assertEquals(comments.get(1).get("author"), theSecondCommentName);


        System.out.println("Test on adding comment with email successfully finished");
        System.out.println("");

    }

    // pretty long tests (takes 20 second), because wait 5 seconds after adding post, to get create posts in different time
    //comment if you don't want to use it and wait so long every build

    @Test
    public void findByDateDescendingTest() throws InterruptedException {
        System.out.println("Starting test on finding posts by date descending");

        String body = "this is body of post";

        for (int i = 1; i < 5; i++) {
            String permalink = blogPostDAO.
                    addPost("Post number: " + i, body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");
            //waiting 10 seconds
            System.out.println("Waiting 5 seconds before adding new post");
            Thread.sleep(5000);
        }

        List<DBObject> foundedList = blogPostDAO.findByDateDescending(3);

        assertEquals(3, foundedList.size());

        System.out.println(foundedList.get(1).get("title"));

        assertEquals(foundedList.get(1).get("title"), "Post number: 3");

        System.out.println("Test on finding post by permalink successfully finished");
        System.out.println("");

    }

}

Easy way to insert List of Strings

//if, for example, you have some function that takes List of Strings as parameter
//the easiest way (for me) to test this function is to call it in such way

function (Arrays.asList(new String []{"List element 1", "List element 2", "List element 3"}))

Example of coping file

 //this is example of function that takes root to the folder where target file lies, and copy there file
 //Contracts_Postpaid_80.xls to the file Contracts_For_VVL.xls which will be created
    public void copyContractsForVVL(String folder) {

        InputStream inStream = null;
        OutputStream outStream = null;

        try {

            File fileToCopy = new File(folder + "//Contracts_Postpaid_80.xls");
            File copiedFile = new File(folder + "//Contracts_For_VVL.xls");

            inStream = new FileInputStream(fileToCopy);
            outStream = new FileOutputStream(copiedFile);

            byte[] buffer = new byte[1024];

            int length;
            //copy the file content in bytes
            while ((length = inStream.read(buffer)) > 0) {

                outStream.write(buffer, 0, length);

            }

            inStream.close();
            outStream.close();

            System.out.println("File is copied successful!");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

UserDAO covered by jUnit test example

import com.mongodb.*;
import course.UserDAO;
import org.junit.After;
import org.junit.BeforeClass;
import org.junit.Test;

import java.net.UnknownHostException;

import static org.junit.Assert.*;

public class UserDAOTest {
    static MongoClient mongoClient;
    static DB blogDatabase;
    static DBCollection usersCollection;
    static UserDAO userDAO;

    // this method called before all test (once), to initialize DB variables
    @BeforeClass
    public static void setUp() throws UnknownHostException {
        mongoClient = new MongoClient();
        blogDatabase = mongoClient.getDB("blog");
        usersCollection = blogDatabase.getCollection("users");
        userDAO = new UserDAO(blogDatabase);
        usersCollection.drop();
    }

    //this method calls after every test to clean usersCollection
    @After
    public void dropCollection(){
        usersCollection.drop();
    }

    @Test
    public void addUserTest()  {
        System.out.println("Starting test on adding user");

        String email =  "b1102@email.ru";

        userDAO.addUser("b1102" , "1111" , email );

        DBObject find   = new BasicDBObject("_id" , "b1102" );

        DBObject user = usersCollection.find(find).next();

        assertTrue(user.get("email").equals(email));

        System.out.println("User successfully added");
        System.out.println("");

    }

    @Test
    public void addUserThatExistsTest() throws UnknownHostException {
        System.out.println("Starting test on adding user that already exists");

        String email =  "b1102@email.ru";

        userDAO.addUser("b1102", "1111", email);
        assertFalse(userDAO.addUser("b1102", "dedede", "e3r3r"));

        System.out.println("Test on adding user that already exists passed");
        System.out.println("");

    }

    @Test
    public void CheckUserPassPositive() throws UnknownHostException {
        System.out.println("Starting positive test on checking user's pass");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        DBObject validatedUser = userDAO.validateLogin(login , "1111");
        DBObject foundUser = usersCollection.find(new BasicDBObject("_id" , login)).next();

        assertEquals(foundUser, validatedUser);

        System.out.println("Positive test on checking user's pass passed");
        System.out.println("");

    }

    @Test
    public void CheckUserPassNegative() throws UnknownHostException {
        System.out.println("Starting negative test on checking user's pass");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        assertNull(userDAO.validateLogin(login , "1112"));

        System.out.println("Negative test on checking user's pass passed");
        System.out.println("");

    }


    @Test
    public void GetNotExistUser() throws UnknownHostException {
        System.out.println("Starting test on getting not exists user");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        assertNull(userDAO.validateLogin(login+"1" , "1111"));

        System.out.println("Test on getting not exists user passed");
        System.out.println("");

    }

}

UserDAO example which is based on MongoDB

/*
Here is an example of UserDAO base on mongoDB
This class can add user and validate passwords for user that already exists
In the next post there is exmap how to cover this class with jUnit tests
*/

package course;

import com.mongodb.*;
import sun.misc.BASE64Encoder;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.security.SecureRandom;
import java.util.Random;

public class UserDAO {
    private final DBCollection usersCollection;
    private Random random = new SecureRandom();

    public UserDAO(final DB blogDatabase) {
        usersCollection = blogDatabase.getCollection("users");
    }

    // validates that username is unique and insert into db
    public boolean addUser(String username, String password, String email) {

        String passwordHash = makePasswordHash(password, Integer.toString(random.nextInt()));

        DBObject newUser = new BasicDBObject("password", passwordHash).append("_id", username);

        if (email != null && !email.equals("")) {
            newUser = new BasicDBObject("password", passwordHash).append("_id", username).append("email", email);
        }

        try {
            usersCollection.insert(newUser);
            return true;
        } catch (MongoException.DuplicateKey e) {
            System.out.println("Username already in use: " + username);
            return false;
        }
    }

    public DBObject validateLogin(String username, String password) {
        DBObject user = null;

        // XXX look in the user collection for a user that has this username
        // assign the result to the user variable.
       try{
        user = usersCollection.find(new BasicDBObject("_id", username)).next();
       } catch (RuntimeException e){

       }
        if (user == null) {
            System.out.println("User not in database");
            return null;
        }

        String hashedAndSalted = user.get("password").toString();

        String salt = hashedAndSalted.split(",")[1];

        if (!hashedAndSalted.equals(makePasswordHash(password, salt))) {
            System.out.println("Submitted password is not a match");
            return null;
        }
        return user;
    }


    private String makePasswordHash(String password, String salt) {
        try {
            String saltedAndHashed = password + "," + salt;
            MessageDigest digest = MessageDigest.getInstance("MD5");
            digest.update(saltedAndHashed.getBytes());
            BASE64Encoder encoder = new BASE64Encoder();
            byte hashedBytes[] = (new String(digest.digest(), "UTF-8")).getBytes();
            return encoder.encode(hashedBytes) + "," + salt;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException("MD5 is not available", e);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("UTF-8 unavailable?  Not a chance", e);
        }
    }
}

MongoDB find in the database from java - example

//Your need mongoDB driver for this!!!!!!!!!!!

package second;

import com.mongodb.*;

import java.net.UnknownHostException;
import java.util.Random;

public class Query {
    public static void main(String sf[]) throws UnknownHostException {
        //connect to DB on localhost on default port  (27017)
        MongoClient client = new MongoClient();
        //select DB "course"
        DB courseDB = client.getDB("course");
        //use collection "queryTests"
        DBCollection collection = courseDB.getCollection("queryTests");
        //cleaning collection if it is not empty
        collection.drop();

        //populate collection with such kind objects: { "x" : 0 , "y" : 62}
        for (int i = 0; i < 10; i++) {
            collection.insert(new BasicDBObject("x", new Random().nextInt(2)).append("y", new Random().nextInt(100)));
        }

        // for example I want to find all objects that have x=0;  10>=y>=90
        DBObject search = new BasicDBObject("x", 0).append("y", new BasicDBObject("$gte", 10).append("$lte", 90));

        System.out.println("Count from BasicDBObject");
        //just counting such objects
        long k = collection.count(search);
        System.out.println(k);

        System.out.println("Find from BasicDBObject");
        //ask to find all objects that suits "search" BasicDBObject patter, and ak not to retrieve their id's
        DBCursor cursor = collection.find(search, new BasicDBObject("_id", false));
        //print the cursor data
        showDataFromCursor(cursor);

        //the fancy way to do the same is criteria builder, doing absolutely the same (finds all objects that have x=0;  10>=y>=90)
        QueryBuilder queryBuilder = QueryBuilder.start("x").is(0).and("y").greaterThanEquals(10).lessThanEquals(90);

        System.out.println("Count from Criteria");
        long j = collection.count(queryBuilder.get());
        System.out.println(j);

        System.out.println("Find from Criteria");
        DBCursor cursor1 = collection.find(queryBuilder.get(), new BasicDBObject("_id", false));
        showDataFromCursor(cursor1);

    }

    //method that prints out data from cursor
    public static void showDataFromCursor(DBCursor cursor) {

        try {
            while (cursor.hasNext()) {
                DBObject cur = cursor.next();
                System.out.println(cur);
            }
        } finally {
            cursor.close();
        }

    }
}

Mongo shell CRUD examples

Here I want to post some examples of using MongoDB shell. Examples were taken from https://education.10gen.com from M101J course.

Here main CRUD commands usage.

!!!!!!!!!!!!!!!!
Insert
!!!!!!!!!!!!!!!!

db.fruit.insert({name:"apple" , color: "red"}, shape: "round");
insert a document into the "fruit" collection with the attributes of "name" being "apple", "color" being "red", and "shape" being round

!!!!!!!!!!!!!!!!
Find
!!!!!!!!!!!!!!!!

db.users.findOne({username:"dwight"}, {_id:false, email: true});
find one document where the key username is "dwight", and retrieve only the key named email

db.scores.find({type:"essay" , score:50} , {student: true, _id: false});
find all documents with an essay score equal to 50 and only retrieve the student field

db.scores.find({ score : { $gte : 50 , $lte : 60 } } );
finds documents with a score between 50 and 60, inclusive

db.users.find( { name : { $gte : "F" , $lte : "Q" } } );
find all users with name between "F" and "Q"

db.users.find({name:{$regex:"q"}, email:{$exists:true}});
retrieves documents from a users collection where the name has a "q" in it, and the document has an email field

db.users.find({name:{}$type:2})
retrieves documents from a users collection where field name is a String

db.scores.find({$or:[{score:{$lt:50}},{score:{$gt:90}}]});
find all documents in the scores collection where the score is less than 50 or greater than 90

db.people.find({$and : [{name : {$gt :" C"}} , {name : {$regex:"a"}}]});
find all people whose name sorts after the letter "C" and contains the letter "a"

db.products.find( { tags : "shiny" } );
could retrieve: { _id : 42 , name : "Whizzy Wiz-o-matic", tags : [ "awesome", "shiny" , "green" ] } and { _id : 1040 , name : "Snappy Snap-o-lux", tags : "shiny" }
find search in fields values and on top level arrays

db.users.find( { friends : { $all : [ "Joe" , "Bob" ] }, favorites : { $in : [ "running" , "pickles" ] } } )
here example of  $all and $in operators; $all works like kind of "and" in Java, and $in works like kind of "or"
could retrieve: { name : "Cliff" , friends : [ "Pete" , "Joe" , "Tom" , "Bob" ] , favorites : [ "pickles", "cycling" ] }

db.catalog.find({"price":{$gt:10000},"reviews.rating":{$gte:5}});
finds all products that cost more than 10,000 and that have a rating of 5 or better.

db.scores.find({type:"exam"}).sort({score:-1}).skip(50).limit(20);
retrieves exam documents, sorted by score in descending order, skipping the first 50 and showing only the next 20

db.scores.count({type:"essay", score:{$gt:90}})
count the documents in the scores collection where the type was "essay" and the score was greater than 90

!!!!!!!!!!!!!!!!
Update
!!!!!!!!!!!!!!!!

db.foo.update({_id:"Texas"},{population:30000000})
deletes everything in Texas (except _id) and insert {population:30000000}

db.users.update({username:"splunker"}, {$set:{country:"RU"}})
command for updating the country to 'RU' for only user with username:"splunker"

db.users.update({username:"jimmy"} , {$unset:{interests:1}})
deleting jimmy's interests

db.users.update({_id : 0} , {$set : {"a.2" : 5}});
set 3rd element value = 5

db.friends.update( { _id : "Mike" }, { $push : { interests : "skydiving" } } );
add to the "interests" array, element with value "skydiving" from the right hand side

db.friends.update( { _id : "Mike" }, { $pop : { interests : -1 } } );
remove from the "interests" array, the left-most element

db.friends.update( { _id : "Mike" }, { $pushAll: { interests : [ "skydiving" , "skiing" ] } } );
add to the "interests" array, elements with values "skydiving" and "skiing" from the right hand side

db.friends.update( { _id : "Mike" }, { $pull: { interests : "skiing"} } );
remove from the "interests" array, the element with value "skiing"

db.friends.update( { _id : "Mike" }, { $addToSet : { interests : "skydiving" } } );
if element exisits in array, "addToSet" does nothing, otherwise it acts like a "push"

db.scores.update( { score : { $lt: 70 } } , { $inc : { score : 20 } } , { multi : true } );
give every record whose score was less than 70 an extra 20 points

!!!!!!!!!!!!!!!!
Remove
!!!!!!!!!!!!!!!!

db.scores.remove({score : {$lt:60}});
delete every record whose score was less than 60

!!!!!!!!!!!!!!!!
Other
!!!!!!!!!!!!!!!!

db.runCommand { getLastError : 1}
getting result of the last command

Blog's goal

I hope that will use this block as data the storage for myself, but if somebody else will find it useful it will be cool)

So, let's start :-)