Skip to main content

Queries in MongoDB

This activity was made by Robinson Andres Cortes

To complete this activity you must need to now how to create a MongoDB collection and how to add multiple documents.
For this exercise you have to download the following file with all the data you haave to insert into your collection, in this case call users: users

Level 1 – Basic Queries​

  1. List all documents from the users collection.
Check if I’m right
db.users.find()
  1. Display only the first_name, last_name, and email fields.
Check if I’m right
db.users.find({}, {
  first_name:1,
  last_name:1,
  email:1,
  _id:0
})
  1. Retrieve all users whose role is "admin".
Check if I’m right
db.users.find({role:"admin"})
  1. Find users whose country is "Colombia".
Check if I’m right
db.users.find({country:"Colombia"})
  1. List users who are active (is_active = true).
Check if I’m right
db.users.find({is_active:1})
  1. Find users who are not verified (is_verified = false).
Check if I’m right
db.users.find({is_verified:0})
  1. Retrieve users whose gender is "Masculino".
Check if I’m right
db.users.find({gender:"Masculino"})
  1. List users who live in the city "Medellín".
Check if I’m right
db.users.find({city:"Medellín"})
  1. Find users who have at least one child (children_count > 0).
Check if I’m right
db.users.find({children_count:{$gt: 0}})
  1. List users whose profession (profession) is not null.
Check if I’m right
db.users.find({profession: {$ne: null}})

Level 2 – Filters with Operators​

  1. Find users with monthly_income greater than 3,000,000.
Check if I’m right
db.users.find({monthly_income:{$gt:3000000}})
  1. Find users with income between 2,000,000 and 5,000,000.
Check if I’m right
db.users.find({monthly_income: {$gte:2000000, $lte:5000000}})
  1. Find users whose birth date is later than 2000-01-01.
Check if I’m right
db.users.find({birth_date: {$gt: new Date('2000-01-01')}})
  1. Find users whose document_type is in ["CC", "CE"].
Check if I’m right
db.users.find({document_type: {$in:["CC", "CE"]}})
  1. Find users whose city is not "Bogotá".
Check if I’m right
db.users.find({city:{$ne:"Bogotá"}})
  1. Find users whose name starts with the letter "A".
Check if I’m right
db.users.find({first_name:{$regex:/^A/}})
  1. Find users whose email ends with "gmail.com".
Check if I’m right
db.users.find({email:{$regex:/example.com$/}})
  1. Find users who have more than 2 children and are active.
Check if I’m right
db.users.find({$and: [{children_count: {$gte:2}}, {is_active:1}]})
  1. Find users whose marital_status is "Casado" and have children.
Check if I’m right
db.users.find({$and: [{marital_status:"Casado"},{children_count:{$gt:0}}]})
  1. Find users who are inactive or not verified.
Check if I’m right
db.users.find({$or:[{is_active:0},{is_verified:0}]})

Level 3 – Sorting and Pagination​

  1. List users ordered by monthly_income from highest to lowest.
Check if I’m right
db.users.find().sort({monthly_income:-1})
  1. Retrieve the 5 most recent users based on created_at.
Check if I’m right
db.users.find().sort({created_at:-1}).limit(5)
  1. Implement pagination: display page 2 with 10 records per page.
Check if I’m right
db.users.find().skip(10).limit(10)
  1. Display full name concatenated (first_name + last_name) and city using aggregation.
Check if I’m right
db.users.find({}, {full_name:{$concat:["$first_name", " ", "$last_name"]}, city:1})
  1. List users ordered by birth date from youngest to oldest.
Check if I’m right
db.users.find().sort({birth_date:-1})

Level 4 – Aggregation Framework​

  1. Calculate the average income (monthly_income) of all users.
Check if I’m right
db.users.aggregate({
  $group:{
    _id:null,
    monthly_avg_income:{$avg:"$monthly_income"}
  }
})
  1. Calculate the average income per city.
Check if I’m right
db.users.aggregate({
  $group:{
    _id:"$city",
    monthly_avg_income:{$avg:"$monthly_income"}
  }
})
  1. Count how many users there are per role.
Check if I’m right
db.users.aggregate({
  $group:{
    _id:"$role",
    count: {$sum:1}
  }
})
  1. Count how many users are active vs inactive.
Check if I’m right
db.users.aggregate({
  $group:{
    _id:"$is_active",
    count: {$sum:1}
  }
})
  1. Get the total number of children grouped by state.
Check if I’m right
db.users.aggregate({
  $group:{
    _id:"$state",
    children:{$sum:"$children_count"}
  }
})