Microsoft Azure Advent Calendar 2015 6日目です。
DocumentDB の SQLクエリ について入門的なことを書きます。
Azure Cosmos DB Query Playgroundで試すこともできますし、Azure の新しい方のポータルで、アカウントを作って試すことができます。
（ポータルで試すともちろん課金が発生します）
> Environment
2015/12時点でのAzureポータル & DocumentDBの仕様
> 事前準備
Azureのポータルで、データベースアカウントを作成し、データベースを作成します。
そして、SampleCollectionというコレクションを作成しています。
作成はポータルで直感的にできますが、ここら辺を参考にしていただければと。
azure.microsoft.com
一応書いておくと、「コレクション」とは、リレーショナルデータベース（以下RDB）でいうところのテーブルの概念です。ただ、DocumentDB の課金はコレクション単位なので、使い方やスキーマの考え方は、同じと思わないほうが良いです。
コレクションを作成した後は、こんなdocumentを投入してます。
[ { "id": "P1", "firstName": "Dan", "lastName": "gerous", "friends": [ "P2", "P1" ], "phone": [ { "home": "03-1111-1111", "mobile": "080-111-1111" } ] }, { "id": "P2", "firstName": "Dan", "lastName": "dy", "friends": [ "P1", "P3" ], "phone": [ { "home": "03-2222-2222", "mobile": "080-222-222" } ] }, { "id": "P3", "firstName": "Per", "lastName": "vert", "friends": [ "P2", "P4" ], "phone": [ { "home": "03-3333-3333", "mobile": "080-333-333" } ] }, { "id": "P4", "firstName": "big", "lastName": "boobs", "friends": [ "P1", "P3" ], "phone": [ { "home": "03-4444-4444", "mobile": "080-444-4444" } ] } ]
Azureポータル からjsonのファイルをインポートすることができますが、現時点だと、1ファイル＝1documentじゃないとインポートできないようです（もしかしたら私のスキーマが変なのか？）
> 基本
documentも用意できたところで本題です。
RDBの基本的なSQLクエリを知っている方であれば、だいたいそんな感じです。
SQLクエリを実行する際、RDBだとデータベースに対してアクセスしますが、
この DocumentDB の場合は、コレクションに対してアクセスします。
何が言いたいかというと、基本的なSELECT句で、
select * from c
を実行すると、対象のコレクションのすべてのdocumentを取得します。
RDBだと「from」の後にテーブル名を指定しますが、
DocumentDB だと単一のコレクションにアクセスしていますので、
「from」の後は、コレクション名を指定しません。
というより、何を書いても大丈夫です（予約語使ったらそれは死んじゃいますが）。
select ... from ...の後は、
「where」ですね。
これもある程度RDBのSQLと似た感じで使えますが、「like」は使えません。
...まぁ、スキーマフリーのデータにSQLっぽいのが使えるだけでもありがたいと思うべきでしょうか。。。。。
「select」句で「*」以外を使う場合は、こんな感じです。
SELECT c.id,c.firstName, c.lastName FROM c where c.firstName ="Dan"
出力結果
[ { "id": "P1", "firstName": "Dan", "lastName": "gerous" }, { "id": "P2", "firstName": "Dan", "lastName": "dy" } ]
大文字や小文字を間違えると、死亡する点は注意です。
firstNameとLastNameを結合したい場合は、「c.firstName + c.lastName」とは書けません。
そんな時は、Built-in functionを使います。今回は文字列をconcatしてくれる「CONCAT」を使っています。
SELECT c.id, CONCAT(c.firstName, c.lastName) FROM c where c.firstName ="Dan"
出力結果
[ { "id": "P1", "$1": "Dangerous" }, { "id": "P2", "$1": "Dandy" } ]
built-in functionを使うと出力された結果が「$1」とか出ちゃうのは微妙なので、AS句を指定してあげましょう。
あと、firstNameとlastNameの間にスペースを入れてあげましょう。
SELECT c.id, CONCAT(c.firstName, " ", c.lastName) AS fullName FROM c where c.firstName ="Dan"
出力結果
[ { "id": "P1", "fullName": "Dan gerous" }, { "id": "P2", "fullName": "Dan dy" } ]
Built-in functionは数学的なやつもたくさん用意されていますので、多少複雑なことをする場合は以下を参考にできることを整理しておいたほうが良いです。
https://msdn.microsoft.com/ja-jp/library/azure/dn782250.aspx
>ちょっと実践的に
データを探すときによく使う2つを紹介します。
まずは、Built-in functionの「ARRAY_CONTAINS」
こんな感じで使います。
SELECT c.id,CONCAT(c.firstName, " ", c.lastName) AS fullName FROM c where ARRAY_CONTAINS(c.friends,"P1")
出力結果
[ { "id": "P1", "fullName": "Dan gerous" }, { "id": "P2", "fullName": "Dan dy" }, { "id": "P4", "fullName": "big boobs" } ]
配列の中で何かを探したいときには便利です。
ただ、これだと今回のサンプルのdocumentで「phone」の中を調べることができない？感じなので、
そんな時は「JOIN」を使います。
RDBのJOINだと別のテーブルをくっつけるのに使うのが一般的ですが、DocumentDBの場合はちょっと違います。
以下の例を見てみましょう。
SELECT c.id, CONCAT(c.firstName, " ", c.lastName) AS fullName, p.home FROM c join p in c.phone where p.mobile ="080-222-222"
出力結果
[ { "id": "P2", "fullName": "Dan dy", "home": "03-2222-2222" } ]
「JOIN」を使って、mobileの中を覗き込みます。
この「JOIN」を使うことで、azureポータルでデータの検索は大体できると思います（単純なものであれば）。
> その他
（結構前ですが）Azureポータルでは、選択した部分のみのクエリが実行できるようになったり、
「OrderBy」が使えるようになったりで便利になってきました。
Where likeが使えないのは、何かと不便だったりします。
（RDBと同じようなことができることを求めることは、なんか違うよねとか思ってはいますが）
ただ、DocumentDBは他のAzureの機能同様にポンポンUpdateしているので、今後に期待したいです。
最後に、参考情報のリンクを貼っておきます。
https://azure.microsoft.com/ja-jp/documentation/articles/documentdb-sql-query/azure.microsoft.com
https://azure.microsoft.com/ja-jp/documentation/articles/documentdb-sql-query-cheat-sheet/azure.microsoft.com
