Labs Queries

Chapter 4¶

C4L1¶

use sample_training show collections db.zips.findOne()

output.json

{
  _id: ObjectId("5c8eccc1caa187d17ca6ed16"),
  city: 'ALPINE',
  zip: '35014',
  loc: { y: 33.331165, x: 86.208934 },
  pop: 3062,
  state: 'AL'
}

db.zips.count( 
    {
        "pop": { $lt: 1000 }
    }
)

C4L2¶

use sample_training show collections db.trips.findOne()

output.json

{
  _id: ObjectId("572bb8222b288919b68abf5a"),
  tripduration: 379,
  'start station id': 476,
  'start station name': 'E 31 St & 3 Ave',
  'end station id': 498,
  'end station name': 'Broadway & W 32 St',
  bikeid: 17827,
  usertype: 'Subscriber',
  'birth year': 1969,
  'start station location': { type: 'Point', coordinates: [ -73.97966069, 40.74394314 ] },
  'end station location': { type: 'Point', coordinates: [ -73.98808416, 40.74854862 ] },
  'start time': ISODate("2016-01-01T00:00:45.000Z"),
  'stop time': ISODate("2016-01-01T00:07:04.000Z")
}

db.trips.count(
    {
        "birth year": {$eq : 1998}
    }
)

db.trips.count(
    {
        "birth year": {$gt : 1998}
    }
)

C4Q1¶

use sample_training show collections db.inspections.findOne()

output.json

{
  _id: ObjectId("56d61033a378eccde8a8354f"),
  id: '10021-2015-ENFO',
  certificate_number: 9278806,
  business_name: 'ATLIXCO DELI GROCERY INC.',
  date: 'Feb 20 2015',
  result: 'No Violation Issued',
  sector: 'Cigarette Retail Dealer - 127',
  address: { city: 'RIDGEWOOD', zip: 11385, street: 'MENAHAN ST', number: 1712 }
}

db.inspections.count(
    {
        "result" : "Out of Business",
        "sector" : "Home Improvement Contractor - 100"
    }
)

C4L3¶

How many companies in the sample_training.companies dataset were

either founded in 2004 // year [and] either have the social category_code [or] web category_code, // cat_social, cat_web, cat_search

[or] were founded in the month of October // month [and] also either have the social category_code [or] web category_code? // cat_social, cat_web, cat_search

wrong.json

db.companies.find(
    {
        $and:
        [
            {founded_year : 2004},
            {
                $or:
                [
                    {category_code: "social"},
                    {category_code: "web"},
                    {founded_month: 10}
                ]
            },
            {
                $or:
                [
                    {category_code: "social"},
                    {category_code: "web"},                   
                ]
            }
        ]
    }
)
.count()

Using Divide and Conquer...

year = { founded_year : 2004 };
month = { founded_month : 10 };
cat_social = { category_code : "social" };
cat_web = { category_code : "web" };
cat_search = { $or: [ cat_social, cat_web ] }

firstSub = { $and: [year,cat_search]}
secondSub = { $and: [month,cat_search]}
query = { $or: [firstSub, secondSub]  }

db.companies.countDocuments(query)

C4L4¶

use sample_training show collections db.zips.findOne()

output.json

{
  _id: ObjectId("5c8eccc1caa187d17ca6ed16"),
  city: 'ALPINE',
  zip: '35014',
  loc: { y: 33.331165, x: 86.208934 },
  pop: 3062,
  state: 'AL'
}

db.zips.count(
    {
        "$nor": [
            { "pop": { "$lt": 5000}  },
            { "pop": { "$gt": 1000000 } }
        ]
    }
)

C4L5¶

How many companies in the sample_training.companies collection have the same permalink as their twitter_username?

db.companies.find( { "$expr": { "$eq": [ "$permalink", "$twitter_username"] } } ).count()

C4L6¶

accomVal = { accommodates : {$gte : 6} };
reviewCount = { number_of_reviews : {$eq : 50} };
projection = { name : 1 };
query = { $and: [accomVal, reviewCount] };

db.listingsAndReviews.find(query, { name : 1 })

C4L7¶

Using the sample_airbnb.listingsAndReviews collection find out how many documents have the "property_type" "House", and include "Changing table" as one of the "amenities"?

reqAmenities = { "amenities" : { $all : ["Changing table"]} };
reqPropertyType = { "property_type" : {$eq: "House"} }
query = { $and: [reqAmenities, reqPropertyType]}

db.listingsAndReviews.count(query)

"amenities": { "$size": 20, "$all": [ "Internet", "Wifi", "Kitchen", "Heating", "Family/kid friendly", "Washer", "Dryer", "Essentials", "Shampoo", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace" ] }

Chapter 5¶

C5L1¶

sample_airbnb.listingsAndReviews

use sample_airbnb

db.listingsAndReviews.aggregate([
                                  { "$project": { "room_type": 1, "_id": 0 }},
                                  { "$group": { "_id": "$room_type" } }
                                ])

C5L2¶

use sample_training db.trips.findOne()

db.trips.find({ "birth year": { "$ne": "" }}).sort({ "birth year": 1 }).limit(1)