Cara menggunakan mongodb group to array

January 3, 2018 by Robert Gravelle

Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In today's blog, we'll use it to write a query to fetch a list of actors that appeared in movies released during a given year.

The Source Database

The query that we'll be building will run against the Sakila sample database. A former member of the MySQL AB documentation team named Mike Hillyer created the Sakila database specifically for the purpose of providing a standard schema for use in books, tutorials, and articles just like the one you're reading.

The database contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

Opening the Query Builder

You can think of the Query Builder as a tool for building queries visually. It's accessible from the Query Designer screen. Let's bring it up by opening a new query:

• Click the Query icon on the main toolbar, followed by the New Query button from the Object toolbar:

• In the Query Designer, click the Query Builder button to open the visual SQL Builder.

  The database objects are displayed in left pane, whereas the right pane is divided into two portions: the upper Diagram Design pane, and the lower Syntax pane:

Constructing the Actors for Year's Films Query

It's a good idea to select the tables first, so that the Query Builder knows which fields to present for the field list:

• Drag a table/view from the left pane to the Diagram Design pane or double-click it to add it to query. We'll be needing the actor, film_actor, and film tables.
• You can assign table aliases by clicking on "" beside each table. To add the table alias, simply double-click the table name and enter the alias in the Diagram Design pane.

Note how the Query Builder already knows the table relationships. That's because foreign key constraints have already been declared on Table objects:

• To include a field in the query, check the left of the field name in the Diagram Design pane. To include all the fields, click at the left of the object caption. Select the actor first and last names as well as the film title.

Adding WHERE Criteria

Clicking on "" beside the WHERE keyword adds a default WHERE condition of "<--> = <-->".

• Click on the left-hand "<--> = <-->" to select a field. That opens a popup dialog that contains a List of fields as well as an Edit tab.
• Click the List tab and choose the f.release_year field.
• Click OK to close the dialog.
• Next, click on the right-hand "<--> = <-->" to set the release year. This time enter a value of "2006" in the Edit tab. Click OK to close the dialog.
• Click OK to close the Query Builder. You should now see the generated SELECT statement in the Query Editor:

SELECT
a.first_name,
a.last_name,
f.title
FROM
actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON fa.film_id = f.film_id
WHERE
f.release_year = 2006
	

• Click the Run button to execute the query. The results will be sorted by Film title:

Conclusion

Whether you're a novice or experience DBA, Navicat's Query Builder makes writing SELECT queries easier than ever before. In an upcoming blog, we'll get into some of its more advanced features.

Lanjut dari pembahasan sebelumnya disini kita akan mencoba praktek basic collection method dari nosql mongodb, setelah sebelumnya kita praktek membuat crud sederhana dengan collection method find(), insert(), remove(), update(), kita lanjut dengan grouping dan join, sebenarnya nosql itu tidak disesain untuk kebutuhan relational seperti join tapi mongodb menyediakan case jika kita mendadak membutuhkan join, istilah join di mongodb disebut lookup atau $lookup.

Di pembahasan kali ini kita akan mencoba praktek dan membahas sedikit materi nya, di dalam pengolahan data grouping sangat di perlukan baik itu untuk kebutuhan report ataupun kebutuhan lainnya, bisa di bayangkan jika di dalam sql tidak ada yang namanya grouping kita query satu-satu menggunakan kondisi where wkwkwk, okay kita skip dulu aja langsung ke praktek berikut daftar isi dari pembahan kita kali ini.

• Praktek Grouping Data
• Kenapa Ada Join Di Mongodb ?
• Praktek Join Dengan Menggunakan Operator $lookup

Praktek Grouping Data

Di dalam ruang lingkup database (basis data) grouping data sangat di perlukan untuk kebutuhan report biasanya, baik itu berupa aggregate angka atau mengelompokan data berdasarkan kolom yang di ingin kan misalkan. Kita ingin mengelompokan data buku berdasarkan tanggal terbit di sinilah fungsi grouping data di perlukan, mari kita coba praktet dengan menggunakan schema sebelumnya yang sudah kita buat di part 1 langsung saja kita praktek, buat collection baru dengan nama books , seperti biasa untuk GUI kita menggunakan aplikasi nosqlbooster jika teman-teman belum menginstallnya dan lupa cara untuk membuat collections nya bisa baca tutorial pertama nya https://koboykampussukabumi.blogspot.com/2020/11/latihan-mongodb-part-1-crud-dan-operator.html untuk collection books setelah kita membuat nya lalu kita masukan data dummy sebagai berikut

db.books.insert([
    {
        'name':'buku 1',
        'id_kategori':'',
        'tahun_terbit': 2010,
        'penulis': 'tes'
    },{
        'name':'buku 2',
        'id_kategori':'',
        'tahun_terbit': 2010,
        'penulis': 'tes'
    },{
        'name':'buku 3',
        'id_kategori':'',
        'tahun_terbit': 2020,
        'penulis': 'tes 1'
    },{
        'name':'buku 4',
        'id_kategori':'',
        'tahun_terbit': 2020,
        'penulis': 'tes 1'
    }
])

Mungkin kita akan mencoba beberapa skenario untuk praktek nya diantaranya sebagai berikut

• grouping data biasa
• grouping data dengan output custom menggunakan $project
• grouping data dan menghitung jumlahnya
• grouping data dengan kondisi 

Kita coba dengan skenario pertama berikut kode nya

db.books.aggregate([
    {
        // $group perintah untuk grouping
        $group: {
             // _id ini wajib ada sedangkan setelah : itu nama kolom yang akan kita grouping
            _id: '$tahun_terbit'
        }
    }
])

Result nya akan seperti ini

/* 1 */

{

"_id" :

2020

},

/* 2 */

{

"_id" :

2010

}

Skenario kedua grouping dengan custom result

db.books.aggregate([
    {
    	// $group perintah untuk grouping
        $group: {
             // _id ini wajib ada sedangkan setelah : itu nama kolom yang akan kita grouping
            _id: '$tahun_terbit'
            
        }
    },{
    	// custom output untuk ditampilkan nanti berupa json
        $project: {
            // key dan value harus menggunakan $ awalnya
            tahun:"$_id"
        }
    }
])

Result nya akan seperti ini

/* 1 */

{

"_id": 2010,

"tahun": 2010

},

/* 2 */

{

"_id": 2020,

"tahun": 2020

}

Skenario ketiga grouping dan menghitung jumlah data yang di grup

db.books.aggregate([
    {
    	// $group perintah untuk grouping
        $group: {
             // _id ini wajib ada sedangkan setelah : itu nama kolom yang akan kita grouping
            _id: '$tahun_terbit',
            // untuk perhitungan menggunakan $sum 
            total:  { "$sum": 1 }
        }
    },{
    	// custom output untuk ditampilkan nanti berupa json
        $project: {
            // key dan value harus menggunakan $ awalnya
            tahun:"$_id",
            total:"$total"
        }
    }
])

Result nya akan seperti ini

/* 1 */

{

"_id": 2020,

"tahun": 2020,

"total": 2

},

/* 2 */

{

"_id": 2010,

"tahun": 2010,

"total": 2

}

Skenario ke empat grouping dengan menggunakan kondisi

db.books.aggregate([
    // menggunakan $match untuk mencari berdasarkan kondisi kolom tertentu
    { $match : {
        // kolom dan value
        penulis: "tes"
    }},
    {
    	// $group perintah untuk grouping
        $group: {
             // _id ini wajib ada sedangkan setelah : itu nama kolom yang akan kita grouping
            _id: '$tahun_terbit',
            total:  { "$sum": 1 }
        }
    },{
    	// custom output untuk ditampilkan nanti berupa json
        $project: {
            // key dan value harus menggunakan $ awalnya
            tahun:"$_id",
            total:"$total"
        }
    }
])

Result nya akan seperti ini

{

"_id": 2010,

"tahun": 2010,

"total": 2

}

Sofar mungkin itu adalah praktek sederhana nya, teman-teman bisa membuka dokumentasi lengkapnya di situs resminya https://docs.mongodb.com/manual/reference/operator/aggregation/group/ disitu sudah lengkap dengan berbagai operator nya disini kita baru mempraktekan dengan operator $match untuk pengkondisian atau filter ketika akan menampilkan data dan juga $sum untuk menghitung jumlah data sesuai yang kita kelompokan.

Saran untuk teman-teman supaya bisa lebih paham lanjtukan dengan ber eksperimen membuat collection baru dan coba untuk grouping data lagi dan mencoba dengan operator lain yang ada di situs nya supaya mengasah kemapuan teman-teman untuk lebih ahli lagi.

Kenapa ada join di mongodb ?

Mungkin akan timbul pertanyaan seperti itu mongodb kan bukan relasional jadi tidak mungkin ada join, ini juga merupakan salah satu faktor yang menjadi kan mongodb populer di lingkup nosql karena ada fitur join layaknya relasional database. Dalam satu kasus penggunaan join ini sangat tidak disarankan karena ya balik lagi ke prinsip dasar jika datanya relasional berarti menggunakan database yang mendukung relasional jika data sudah di normalisasi kan semua ke dalam satu tabel atau collection nosql bisa menjadi pilihan.

Penggunaan join ini hanya di gunakan untuk kasus jika aplikasi udah jalan dan jadi sedangkan jika ada kasus untuk kebutuhan datanya perlu di pisah collection nya sehingga membutuhkan join disinilah baru peran join bisa digunakan di mongodb, atau mungkin bisa jug menggunakan multi database hehehe, jika dari awal desain kita sudah ok sangat tidak disaranakan sih untuk menggunakan join karena akan lemot dari segi perpormance nya. Gambaran query join di mongodb proses nya  akan seperti pada gambar di bawah ini

Mungkin nanti akan timbul pertanyaan kenapa membuat tutorial nya jika tidak disarankan ?, nah jawaban nya yaitu untuk case jika sistem kita sudah besar dan nanti ada kasus butuh memisahkan collection satu dengan collection lain nya tapi saling berhubungan ini semua berdasarkan pengalaman pribadi sih hehehe. Tutorial ini hanya sebagai pembelajaran aja bagi kita supaya tepat dalam memilih, memecahkan masalah dalam kondisi tertentu di nosql mongodb.

Untuk dokumentasi lengkapnya ada disitus ini silahkan teman-teman pelajari dan baca-baca di situs resminya https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html

Praktek join dengan menggunakan operator $lookup

Langkah pertama buat collection baru lagi dengan nama category dan buat data dummy nya seperti ini

db.category.insert([{
    'name': 'teknologi',
}, {
    'name': 'informasi',
}])

setelah kita punya collection baru dengan nama category schema tersebut nanti akan saling berelasi dengan schema books mari kita ubah salah satu data yang ada di schema books dengan memasukan ObjectId(_id) dari collection category ke books.

db.books.update({ _id: ObjectId("5fec273679670010168296cd") }, {
    $set: {
        "name": "buku 3",
        "id_kategori": ObjectId("5ff2692af1d6de2fdc6aca7b"),
        "tahun_terbit": 2020,
        "penulis": "tes 1"
    }
})

sebagai catatan perlu di ingat jika ingin membuat relasi menggunakan kolom _id tipe data nya wajib ObjectId jika tidak maka tidak akan bisa di relasi

Mari kita praktek berikut adalah contoh code nya untuk join di mongodb

db.books.aggregate([
{
    $lookup:
     {
       from: 'category', // collection yang akan kita join
       localField: 'id_kategori', // id category di simpan di kolom books
       foreignField: '_id', // id di collection category
       as: 'kategori' // alias result column
     }
}    
])

Sehingga resultnya akan menjadi seperti ini

Mungkin akan timbul pertanyaan bagaimana supaya result nya saya hanya ingin nama kategori nya saja berikut adalah contoh dari skenario selanjutnya,

db.books.aggregate([
    {
        $lookup:
        {
            from: 'category',
            localField: 'id_kategori',
            foreignField: '_id',
            as: 'kategori'
        },
    }, {
        $project: {
            "book_name": "$name",
            "book_category_name": "$kategori.name"
        }
    }
])

Hasil result dari skenario kita hanya mengambil beberapa kolom saja dan bsa mendefinisikan output yang akan kita kirim apa saja menggunakan $project mungkin akan timbul pertanyaan lagi saya ingin output dari kategorinya bukan berupa array, berikut adalah contoh code skenario terakhir

db.books.aggregate([
    {
        $lookup:
        {
            from: 'category',
            localField: 'id_kategori',
            foreignField: '_id',
            as: 'kategori'
        },
    },
    { $unwind: "$kategori" }, {
        $project: {
            "book_name": "$name",
            "book_category_name": "$kategori.name"
        }
    }
])

Yos sampain disini praktek join di mongodb sudah selesai mungkin segitu yang bisa saya sampaikan pada tulisan kali ini semoga bermanfaat bagi teman-teman, jika ada yang tidak mengerti, ada kesalahan dalam penulisan dan ada yang ingin ditanyakan atau di tambahkan bisa tulis dikolom komentar terimakasih.