MongoDB for developers 2/8. Crud

What does the following fragment of JavaScript output?

x = { "a" : 1 };
y = "a";
x[y]++;
print(x.a);

2

BSON Introduced

Which of the following are types available in BSON?

Strings

Floating-point numbers

Arrays

Objects

Timestamps

Mongo shell, inserting docs

Insert a document into the "fruit" collection with the attributes of "name" being "apple", "color" being "red", and "shape" being round. Assume that we have already issued the use command to get into the right database. Use the "insert" method.

db.fruit.insert({name: "apple", color:"red", shape:"round"})

Mongo shell, introdution to findOne

Use findOne on the collection users to find one document where the key usernameis "dwight", and retrieve only the key named email.

db.users.findOne({username:"dwight"}, {email: true, _id:false})

Mongo shell, querying using field selection

Supposing a scores collection similar to the one presented, how would you find all documents with an essay score equal to 50 and only retrieve the student field?

db.scores.find({type:"essay", score: 50}, {_id:false, student:true})

Querying using $gt and $lt

Which of these finds documents with a score between 50 and 60, inclusive?

db.scores.find({ score : { $gte : 50 , $lte : 60 } } );

Inequalities on strings

Which of the following will find all users with name between "F" and "Q"?

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

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

Using regexes, $exists, $type

Write a query that retrieves documents from a users collection where the namehas a "q" in it, and the document has an email field.

db.users.find({name: {$regex: "q"}, email: {$exists: true}})

Using $or

How would you find all documents in the scores collection where the score is less than 50 or greater than 90?

db.scores.find({ $or: [ {score: {$lt: 50}}, {score:{$gt: 90}} ]})

Using $and

What will the following query do?

db.scores.find( { score : { $gt : 50 }, score : { $lt : 60 } } );

Find all documents with score less than 60 (the first query was ignored by js)

Querying inside arrays

Which of the following documents would be returned by this query?

db.products.find( { tags : "shiny" } );

{ _id : 42 , name : "Whizzy Wiz-o-matic", tags : [ "awesome", "shiny" , "green" ] }

{ _id : 1040 , name : "Snappy Snap-o-lux", tags : "shiny" }

Using $in and $all

Which of the following documents matches this query?

db.users.find( { friends : { $all : [ "Joe" , "Bob" ] }, favorites : { $in : [ "running" , "pickles" ] } } )

{ name : "Cliff" , friends : [ "Pete" , "Joe" , "Tom" , "Bob" ] , favorites : [ "pickles", "cycling" ] }

Queries with dot notation

Suppose a simple e-commerce product catalog called catalog with documents that look like this:

{ product : "Super Duper-o-phonic", 
  price : 100000000000,
  reviews : [ { user : "fred", comment : "Great!" , rating : 5 },
              { user : "tom" , comment : "I agree with Fred, somewhat!" , rating : 4 } ],
  ... }

Write a query that finds all products that cost more than 10,000 and that have a rating of 5 or better.

db.catalog.find({"price": {$gt: 10000}, "reviews.rating": {$gte: 5}})

Querying, cursors

Recall the documents in the scores collection:

{
 "_id" : ObjectId("50844162cb4cf4564b4694f8"),
 "student" : 0,
 "type" : "exam",
 "score" : 75
}

Write a query that retrieves exam documents, sorted by score in descending order, skipping the first 50 and showing only the next 20.

db.scores.find({type: "exam"}).sort({score: -1}).skip(50).limit(20);

Counting results

How would you count the documents in the scores collection where the type was "essay" and the score was greater than 90?

db.scores.count({type:"essay", score:{$gt: 90}})

Wholesale Updating of a Document

Let's say you had a collection with the following document in it:

{ "_id" : "Texas", "population" : 2500000, "land_locked" : 1 }

and you issued the query:

db.foo.update({_id:"Texas"},{population:30000000})

What would be the state of the collection after the update?

{ "_id" : "Texas", "population" : 30000000 }

Using the $set command

Given the document

{'username':'splunker', 'country':'US', 'phone':'718-343-3433'}

in the collection users, write the shell command for updating the country to 'RU' for only this user.

db.users.update({username: "splunker"}, {$set: {country: "RU"}} )

Using the $unset command

Write an update query that will unset the interests key in the following document in the collection users. The primary key is username.

{'username':'jimmy', favorite_color:'blue', interests:['debating', 'politics']}

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

Using $push, $pop, $pull, $pushAll, $pullAll, $addToSet

Suppose you have the following document in your friends collection:

{ _id : "Mike", interests : [ "chess", "botany" ] }

What will the result of the following updates be?

db.friends.update( { _id : "Mike" }, { $push : { interests : "skydiving" } } );
db.friends.update( { _id : "Mike" }, { $pop : { interests : -1 } } );
db.friends.update( { _id : "Mike" }, { $addToSet : { interests : "skydiving" } } );
db.friends.update( { _id : "Mike" }, { $pushAll: { interests : [ "skydiving" , "skiing" ] } } );

{ _id : "Mike", interests : [ "botany","skydiving","skydiving" , "skiing" ] }

Multi-update

Recall the schema of the scores collection:

{
 "_id" : ObjectId("50844162cb4cf4564b4694f8"),
 "student" : 0,
 "type" : "exam",
 "score" : 75
}

How would you give every record whose score was less than 70 an extra 20 points?

db.scores.update({score: {$lt: 70}}, {$inc: {score: 20}}, {multi: true})

Removing data

Recall the schema of the scores collection:

{
 "_id" : ObjectId("50844162cb4cf4564b4694f8"),
 "student" : 0,
 "type" : "exam",
 "score" : 75
}

How would you delete every record whose score was less than 60?

db.scores.remove({score: {$lt:60}})

Java driver: representing documents

How would you create a document using the Java driver with this JSON structure:

{
   "_id" : "user1",
   "interests" : [ "basketball", "drumming"]
}

new BasicDBObject("_id", "user1").append("interests", Arrays.asList("basketball", "drumming"));

Java driver: insert

Do you expect the second insert below to succeed?

        MongoClient client = new MongoClient();
        DB db = client.getDB("school");
        DBCollection people = db.getCollection("people");

        DBObject doc = new BasicDBObject("name", "Andrew Erlichson")
                .append("company", "10gen");

        try {
            people.insert(doc);      // first insert
            doc.removeField("_id");  // remove the "_id" field
            people.insert(doc);      // second insert
        } catch (Exception e) {
            e.printStackTrace();
        }

Yes, because the removeField call will remove the _id key added by the driver in the first insert

Java driver: find, findOne, count

In the following code snippet:

        MongoClient client = new MongoClient();
        DB db = client.getDB("school");
        DBCollection people = db.getCollection("people");
        DBObject doc;
        xxxx
        System.out.println(doc);

Please enter the simplest one line of Java code that would be needed in place of xxxx to make it print one document from the people collection.

doc = people.findOne();

Java driver: query criteria

Given a collection of documents with two fields -- type and score -- what is the correct line of code to find all documents where type is "quiz" and score is greater than 20 and less than 90. Select all that apply.

scores.find(new BasicDBObject("type", "quiz").append("score", new BasicDBObject("$gt", 20).append("$lt", 90)))

scores.find(QueryBuilder.start("type").is("quiz").and("score").greaterThan(20).lessThan(90).get())

Java driver: field selection

Given a variable named "students" of type DBCollection, which of the following lines of code could be used to find all documents in the collection, retrieving only the "phoneNumber" field.

students.find(new BasicDBObject(), new BasicDBObject("phoneNumber", 1).append("_id", 0))

Java driver: dot notation

In the following code snippet, what do you think will happen if there exists in the collection a document that matches the query but does not have a key called "media.url"?

DBObject findOneUrlByMediaType(DBCollection videos, String mediaType) {
    DBObject query = new BasicDBObject("media.type", mediaType);
    DBObject projection = new BasicDBObject("media.url", true);
   
    return videos.findOne(query, projection);   
}  

It will return a document containing a single field containing the document's _id

Java driver: sort, skip and limit

Supposed you had the following documents in a collection named things.

{ "_id" : 0, "value" : 10 }
{ "_id" : 1, "value" : 5 }
{ "_id" : 2, "value" : 7 }
{ "_id" : 3, "value" : 20 }

If you performed the following query in the Java driver:

collection.find().sort(new BasicDBObject("value", -1)).skip(2).limit(1);

which document would be returned?

The document with _id=2

Java driver: update and remove

In the following code fragment, what is the Java expression in place of xxxx that will set the field "examiner" to the value "Jones" for the document with _id of 1. Please use the $set operator.

        # update using $set
        scores.update(new BasicDBObject("_id", 1), xxxx);

new BasicDBObject($set, new BasicDBObject("examiner","Jones"))