[DevHub] From SQL to MongoDB: Aggregation Pipeline

albums.mongdb

// MongoDB Playground
// To disable this template go to Settings | MongoDB | Use Default Template For Playground.
// Make sure you are connected to enable completions and to be able to run a playground.
// Use Ctrl+Space inside a snippet or a string literal to trigger completions.

// Select the database to use.
use('mongodbVSCodePlaygroundDB');

// The drop() command destroys all data from a collection.
// Make sure you run it against proper database and collection.
db.albums.drop();

// Insert a few documents into the albums collection.
db.albums.insertMany([
  { 'name' : 'lo-fi chill hop songs to study to', band_name: 'Silicon Infinite', price: 7.99, status: 'A', 
    songs: [
      { title: 'Snow beats', 'plays': 133 },
      { title: 'Rolling By',  'plays': 242 },
      { title: 'Sway',  'plays': 3191 }
    ]
  },
  { 'name' : 'Moon Rocks', band_name: 'Silicon Infinite', price: 1.99, status: 'B', 
    songs: [
      { title: 'Milk Toast', 'plays': 118 },
      { title: 'Purple Mic',  'plays': 719 },
      { title: 'One Note Dinner Party',  'plays': 1242 }
    ]
  },
  { 'name' : 'Flavour', band_name: 'Organical', price: 4.99,  status: 'A', 
    songs: [
      { title: 'But First Coffee', 'plays': 562 },
      { title: 'Autumn',  'plays': 901 }
    ]
  },
]);

// Count all records from albums
db.albums.aggregate([
  {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
]);

// Sum the price field from orders
db.albums.aggregate( [
   {
     $group: {
        _id: null,
        total: { $sum: "$price" }
     }
   }
] )

// For each unique band_name, sum the price field.
db.albums.aggregate( [
   {
     $group: {
        _id: "$band_name",
        total: { $sum: "$price" }
     }
   }
] )

// For each unique band_name, sum the price field, results sorted by sum.
db.albums.aggregate( [
   {
     $group: {
        _id: "$band_name",
        total: { $sum: "$price" }
     }
   },
   { $sort: { total: 1 } }
] )

// For band_name with multiple albums, return the band_name and the corresponding album count.
db.albums.aggregate( [
   {
     $group: {
        _id: "$band_name",
        count: { $sum: 1 }
     }
   },
   { $match: { count: { $gt: 1 } } }
] )

// For each unique band_name grouping, sum the price field and return only where the sum is greater than 5.00. Excludes the time portion of the date.
db.albums.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$band_name",
        },
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 5.00 } } }
] )


// For each unique band_name with status A, sum the price field.
db.albums.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$band_name",
        total: { $sum: "$price" }
     }
   }
] )

// For each unique band_name with status A, sum the price field and return only where the sum is greater than $5.00.
db.albums.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$band_name",
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 5.00 } } }
] )

// For each unique band_name, sum the corresponding song plays field associated with the albums.
db.albums.aggregate( [
   { $unwind: "$songs" },
   {
     $group: {
        _id: "$band_name",
        qty: { $sum: "$songs.plays" }
     }
   }
] )

// For each unique band_name, sum the corresponding song plays field associated with a given band's second album
db.albums.aggregate( [
   { $unwind: "$songs" },
   {
     $group: {
        _id: "$band_name",
        qty: { $sum: "$songs.plays" }
     }
   }
] )

albums.sql

-- Creating the main albums table
CREATE TABLE IF NOT EXISTS albums (
  id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
  name VARCHAR(40) NOT NULL UNIQUE,
  band_name VARCHAR(40) NOT NULL,
  price float8 NOT NULL,
  status VARCHAR(10) NOT NULL
);

-- Creating the songs table
CREATE TABLE IF NOT EXISTS songs (
  id SERIAL PRIMARY KEY NOT NULL,
  title VARCHAR(40) NOT NULL,
  plays integer NOT NULL,
  album_id BIGINT NOT NULL REFERENCES albums ON DELETE RESTRICT
);

INSERT INTO albums (name, band_name, price, status)
VALUES
  ('lo-fi chill hop songs to study to', 'Silicon Infinite', 7.99, 'A'),
  ('Moon Rocks', 'Silicon Infinite', 1.99, 'B'),
  ('Flavour', 'Organical', 4.99, 'A');

INSERT INTO songs (title, plays, album_id)
VALUES
  ('Snow Beats', 133, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
  ('Rolling By', 242, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
  ('Clouds', 3191, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
  ('But First Coffee', 562, (SELECT id from albums WHERE name='Flavour')),
  ('Autumn', 901, (SELECT id from albums WHERE name='Flavour')),
  ('Milk Toast', 118, (SELECT id from albums WHERE name='Moon Rocks')),
  ('Purple Mic', 719, (SELECT id from albums WHERE name='Moon Rocks')),
  ('One Note Dinner Party', 1242, (SELECT id from albums WHERE name='Moon Rocks'));
                                   
                                   -- Count all records from albums
SELECT COUNT(*) AS count
FROM albums;

-- Sum the price field from orders
SELECT SUM(price) AS total
FROM albums;

-- For each unique band_name, sum the price field.
SELECT band_name,
  SUM(price) AS total
FROM albums
GROUP BY band_name;

-- For each unique band_name, sum the price field, results sorted by sum.
SELECT band_name,
       SUM(price) AS total
FROM albums
GROUP BY band_name
ORDER BY total;

-- For band_name with multiple albums, return the band_name and the corresponding album count
SELECT band_name,
       count(*)
FROM albums
GROUP BY band_name
HAVING count(*) > 1;

-- For each unique band_name grouping, sum the price field and return only where the sum is greater than 7.00.
SELECT band_name,
  SUM(price) AS total
FROM albums
GROUP BY band_name
HAVING SUM(price) > 7.00;

-- For each unique band_name with status A, sum the price field.
SELECT band_name,
       SUM(price) as total
FROM albums
WHERE status = 'A'
GROUP BY band_name;

-- For each unique band_name with status A, sum the price field and return only where the sum is greater than $7.00.
SELECT band_name,
       SUM(price) as total
FROM albums
WHERE status = 'A'
GROUP BY band_name
HAVING SUM(price) > 7.00;

-- For each unique band_name, sum the corresponding song plays field associated with the albums.
SELECT band_name,
       SUM(songs.plays) as total_plays
FROM albums,
     songs
WHERE songs.album_id = albums.id
GROUP BY band_name;