Queries de pipeline de agregação do MongoDB versus queries SQL

1
-- Creating the main albums table
2
CREATE TABLE IF NOT EXISTS albums (
3
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
4
    name VARCHAR(40) NOT NULL UNIQUE,
5
    band_name VARCHAR(40) NOT NULL,
6
    price float8 NOT NULL,
7
    status VARCHAR(10) NOT NULL
8
);
9
10
-- Creating the songs table
11
CREATE TABLE IF NOT EXISTS songs (
12
    id SERIAL PRIMARY KEY NOT NULL,
13
    title VARCHAR(40) NOT NULL,
14
    plays integer NOT NULL,
15
    album_id BIGINT NOT NULL REFERENCES albums ON DELETE RESTRICT
16
);
17
18
INSERT INTO albums (name, band_name, price, status)
19
VALUES
20
    ('lo-fi chill hop songs to study to', 'Silicon Infinite', 7.99, 'A'),
21
    ('Moon Rocks', 'Silicon Infinite', 1.99, 'B'),
22
    ('Flavour', 'Organical', 4.99, 'A');
23
24
INSERT INTO songs (title, plays, album_id)
25
VALUES
26
    ('Snow Beats', 133, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
27
    ('Rolling By', 242, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
28
    ('Clouds', 3191, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
29
    ('But First Coffee', 562, (SELECT id from albums WHERE name='Flavour')),
30
    ('Autumn', 901, (SELECT id from albums WHERE name='Flavour')),
31
    ('Milk Toast', 118, (SELECT id from albums WHERE name='Moon Rocks')),
32
    ('Purple Mic', 719, (SELECT id from albums WHERE name='Moon Rocks')),
33
    ('One Note Dinner Party', 1242, (SELECT id from albums WHERE name='Moon Rocks'));

1
{
2
    name : 'lo-fi chill hop songs to study to',
3
    band_name: 'Silicon Infinite',
4
    price: 7.99,
5
    status: 'A',
6
    songs: [
7
        { title: 'Snow beats', 'plays': 133 },
8
        { title: 'Rolling By', 'plays': 242 },
9
        { title: 'Sway', 'plays': 3191 }
10
    ]
11
}

1
// Select the database to use.
2
use('mongodbVSCodePlaygroundDB');
3
4
// The drop() command destroys all data from a collection.
5
// Make sure you run it against the correct database and collection.
6
db.albums.drop();
7
8
// Insert a few documents into the albums collection.
9
db.albums.insertMany([
10
    {
11
        'name' : 'lo-fi chill hop songs to study to', band_name: 'Silicon Infinite', price: 7.99, status: 'A',
12
        songs: [
13
            { title: 'Snow beats', 'plays': 133 },
14
            { title: 'Rolling By', 'plays': 242 },
15
            { title: 'Clouds', 'plays': 3191 }
16
        ]
17
    },
18
    {
19
        'name' : 'Moon Rocks', band_name: 'Silicon Infinite', price: 1.99, status: 'B',
20
        songs: [
21
            { title: 'Milk Toast', 'plays': 118 },
22
            { title: 'Purple Mic', 'plays': 719 },
23
            { title: 'One Note Dinner Party', 'plays': 1242 }
24
        ]
25
    },
26
    {
27
        'name' : 'Flavour', band_name: 'Organical', price: 4.99, status: 'A',
28
        songs: [
29
            { title: 'But First Coffee', 'plays': 562 },
30
            { title: 'Autumn', 'plays': 901 }
31
        ]
32
    },
33
]);

1
SELECT COUNT(*) AS count
2
FROM albums

1
db.albums.aggregate( [
2
    {
3
        $group: {
4
            _id: null,  //  An _id value of null on the $group operator accumulates values for all the input documents as a whole.
5
            count: { $sum: 1 }
6
        }
7
    }
8
] );

1
SELECT SUM(price) AS total
2
FROM albums

1
db.albums.aggregate( [
2
    {
3
        $group: {
4
            _id: null,
5
            total: { $sum: "$price" }
6
        }
7
    }
8
] );

1
SELECT band_name,
2
SUM(price) AS total
3
FROM albums
4
GROUP BY band_name

1
db.albums.aggregate( [
2
    {
3
        $group: {
4
            _id: "$band_name",
5
            total: { $sum: "$price" }
6
        }
7
    }
8
] );

1
SELECT band_name,
2
    SUM(price) AS total
3
FROM albums
4
GROUP BY band_name
5
ORDER BY total

1
db.albums.aggregate( [
2
    {
3
        $group: {
4
            _id: "$band_name",
5
            total: { $sum: "$price" }
6
        }
7
    },
8
    { $sort: { total: 1 } }
9
] );

1
SELECT band_name,
2
    count(*)
3
FROM albums
4
GROUP BY band_name
5
HAVING count(*) > 1;

1
db.albums.aggregate( [
2
    {
3
      $group: {
4
         _id: "$band_name",
5
         count: { $sum: 1 }
6
      }
7
    },
8
    { $match: { count: { $gt: 1 } } }
9
 ] );

1
SELECT band_name,
2
    SUM(price) as total
3
FROM albums
4
WHERE status = 'A'
5
GROUP BY band_name

1
db.albums.aggregate( [
2
    { $match: { status: 'A' } },
3
    {
4
        $group: {
5
            _id: "$band_name",
6
            total: { $sum: "$price" }
7
        }
8
    }
9
] );

1
SELECT band_name,
2
    SUM(price) as total
3
FROM albums
4
WHERE status = 'A'
5
GROUP BY band_name
6
HAVING SUM(price) > 5.00;

1
db.albums.aggregate( [
2
    { $match: { status: 'A' } },
3
    {
4
        $group: {
5
            _id: "$band_name",
6
            total: { $sum: "$price" }
7
        }
8
    },
9
    { $match: { total: { $gt: 5.00 } } }
10
] );

1
SELECT band_name,
2
    SUM(songs.plays) as total_plays
3
FROM albums,
4
    songs
5
WHERE songs.album_id = albums.id
6
GROUP BY band_name;

1
db.albums.aggregate( [
2
    { $unwind: "$songs" },
3
    {
4
        $group: {
5
            _id: "$band_name",
6
            qty: { $sum: "$songs.plays" }
7
        }
8
    }
9
] );

1
SELECT name, title, plays
2
    FROM songs s1 INNER JOIN albums ON (album_id = albums.id)
3
WHERE plays=(SELECT MAX(s2.plays)
4
    FROM songs s2
5
WHERE s1.album_id = s2.album_id)
6
ORDER BY name;

1
db.albums.aggregate( [
2
    { $project:
3
        {
4
            name: 1,
5
            plays: {
6
                $filter: {
7
                    input: "$songs",
8
                    as: "item",
9
                    cond: { $eq: ["$item.plays", { $max: "$songs.plays" }] }
10
                }
11
            }
12
        }
13
    }
14
] );