MongoDBでJOINっぽいことをやる

はじめに

MongoDBは、ドキュメント指向NoSQLデータベースです。
RDBでいうところのテーブルをコレクション、レコードをドキュメントといいます。
ドキュメントは、BSON形式で保存されます(シェルでのやりとりはJSONでできる)。
SQLは使えないので、当然JOINそのものはありません。

環境

MongoDB: 4.4.5
OS: Windows

前提(データ設計)

• 仮想のネトゲを考えます。
• ユーザー情報を保存するコレクションがあります。
• 複数のユーザーが所属しうる、ギルドが存在します。
• ギルドにはなんらかのポイントが存在します。

やりたいこと

1. 自分の所属するギルド一覧を取得する。
2. ギルドのランキングを取得する。ランキングには、自分が所属しているギルドかどうかも分かるようにする。

RDB(SQL)のとき

※試してないので間違ってるかも。イメージです。

テーブル設計

ユーザーテーブル(USER)

カラム名	意味
id	ユーザーID
name	ユーザー名

ギルドテーブル(GUILD)

カラム名	意味
id	ギルドID
name	ギルド名

ギルド所属テーブル(GUILD_USER)

カラム名	意味
guild_id	ギルドID
user_id	ユーザーID

自分の所属するギルド一覧を取得する

SELECT * FROM GUILD WHERE ID IN(select guild_id from GUILD_USER where user_id='hoge')

ギルドのランキングを取得する。ランキングには、自分が所属しているギルドかどうかも分かるようにする。

SELECT * FROM GUILD g LEFT JOIN GUILD_USER gu ON g.id=gu.guild_id LEFT JOIN USER u ON gu.user_id=u.id

MongoDBのデータ準備

※最大メンバー数次第ですが通常はギルドとギルド所属のコレクションは分けないと思います。あくまでJOIN実験用ということで。

> mongo
> use test
switched to db test
> db.user.insertMany([{"name":"taro"}, {"name":"jiro"},{"name":"sabro"}])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("6072ff34be34897ee224a2a3"),
                ObjectId("6072ff34be34897ee224a2a4"),
                ObjectId("6072ff34be34897ee224a2a5")
        ]
}
> db.user.find()
{ "_id" : ObjectId("6072ff34be34897ee224a2a3"), "name" : "taro" }
{ "_id" : ObjectId("6072ff34be34897ee224a2a4"), "name" : "jiro" }
{ "_id" : ObjectId("6072ff34be34897ee224a2a5"), "name" : "sabro" }
>
> db.guild.insertMany([{"name":"guild1", "point": 3}, {"name": "guild2", "point": 10}, {"name": "guild3", "point": 0}])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("6072ff97be34897ee224a2a6"),
                ObjectId("6072ff97be34897ee224a2a7"),
                ObjectId("6072ff97be34897ee224a2a8")
        ]
}
> db.guild.find({})
{ "_id" : ObjectId("6072ff97be34897ee224a2a6"), "name" : "guild1", "point" : 3 }
{ "_id" : ObjectId("6072ff97be34897ee224a2a7"), "name" : "guild2", "point" : 10 }
{ "_id" : ObjectId("6072ff97be34897ee224a2a8"), "name" : "guild3", "point" : 0 }
>
> db.guild_user.insertMany([{"guild_id": ObjectId("6072ff97be34897ee224a2a6"), "user_id": ObjectId("6072ff34be34897ee224a2a3")}, {"guild_id": ObjectId("6072ff97be34897ee224a2a6"), "user_id": ObjectId("6072ff34be34897ee224a2a4")}, {"guild_id": ObjectId("6072ff97be34897ee224a2a7"), "user_id": ObjectId("6072ff34be34897ee224a2a4")}])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("60730116be34897ee224a2a9"),
                ObjectId("60730116be34897ee224a2aa"),
                ObjectId("60730116be34897ee224a2ab")
        ]
}
> db.guild_user.find()
{ "_id" : ObjectId("60730116be34897ee224a2a9"), "guild_id" : ObjectId("6072ff97be34897ee224a2a6"), "user_id" : ObjectId("6072ff34be34897ee224a2a3") }
{ "_id" : ObjectId("60730116be34897ee224a2aa"), "guild_id" : ObjectId("6072ff97be34897ee224a2a6"), "user_id" : ObjectId("6072ff34be34897ee224a2a4") }
{ "_id" : ObjectId("60730116be34897ee224a2ab"), "guild_id" : ObjectId("6072ff97be34897ee224a2a7"), "user_id" : ObjectId("6072ff34be34897ee224a2a4") }

自分の所属するギルド一覧を取得する

> db.guild_user.aggregate([
      // jiroの所属ギルド一覧を取得
...   {"$match": {"user_id": ObjectId("6072ff34be34897ee224a2a4")}},
      // jiroの所属ギルドのギルド情報をLEFT JOIN相当
...   {
...     "$lookup": {
...       "from": "guild",
...       "localField": "guild_id",
...       "foreignField": "_id",
...       "as": "guild"
...     }
...   },
      // ギルド情報が配列になっているのでオブジェクトに展開
...   {"$unwind": "$guild"},
      // ギルドIDとギルド名だけを取得
...   {"$project": {"guild_id": 1, "guild_name": "$guild.name", "_id": 0}}
... ])
{ "guild_id" : ObjectId("6072ff97be34897ee224a2a6"), "guild_name" : "guild1" }
{ "guild_id" : ObjectId("6072ff97be34897ee224a2a7"), "guild_name" : "guild2" }

ギルドのランキングを取得する。ランキングには、自分が所属しているギルドかどうかも分かるようにする。

> db.guild.aggregate([
       // ランキング(ポイント降順)でソート
...   {"$sort": {"point": -1}},
       // ギルド所属ユーザーとJOIN
...   {
...     "$lookup": {
...       "from": "guild_user",
          // 変数「gid」をJOIN元(guildコレクション)のフィールド「_id」の値から設定
...       "let": {"gid": "$_id"},
          // JOIN対象のドキュメント検索パイプライン
...       "pipeline": [
...         {
              // 一致の検索
...           "$match": {
...             "$expr": {
                  // guild_idが対応するguildコレクションのドキュメントのIDかつ指定ユーザー(jiro)
...               "$and": [
...                 {"$eq": ["$guild_id", "$$gid"]},
...                 {"$eq": ["$user_id", ObjectId("6072ff34be34897ee224a2a4")]},
...               ]
...             }
...           }
...         },
            // レコード数だけが欲しいので「_id」フィールドのみ取得
...         {"$project": {"_id": 1}},
...       ],
...       "as": "guild"
...     }
...   },
       // ギルド名と、JOIN結果のレコード数から所属有無を取得
...   {
...     "$project": {
...       "name": 1,
...       "my_guild": {
...         "$cond": [{"$eq": [{"$size": "$guild"}, 0]}, false, true]}
...     },
...   }
... ])
{ "_id" : ObjectId("6072ff97be34897ee224a2a7"), "name" : "guild2", "my_guild" : true }
{ "_id" : ObjectId("6072ff97be34897ee224a2a6"), "name" : "guild1", "my_guild" : true }
{ "_id" : ObjectId("6072ff97be34897ee224a2a8"), "name" : "guild3", "my_guild" : false }

感想

• めんどくさい。デバッグや構文エラーがつらい。
• SQLを書くというよりプログラミングをしている感覚に近い。実行計画を手書きしてるような。
• まあでも$lookupを使いまくればとりあえずなんとかなりそうではある。
• ただ正解が分からない。

参考

https://qiita.com/mtitg/items/c342fbd55f6aea047182
https://qiita.com/y4u0t2a1r0/items/d8b7875c727ca54fd792
https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/