AlaSQL.jsの使い方基本｜JSONオブジェクトのSQL操作が出来るJavaScriptライブラリ

2019-06-14 2019-08-28

JSON形式のデータに対して操作を行おうとした時に、JavaScriptで処理を書くのが通常ではありますが、ある項目で合計したり集計したりといった事をループで書くと、結構めんどくさい事があります。

SQLのように操作できればもっと簡単に集計できるのに

そんな事を思った事は無いでしょうか？

JSON形式のデータをSQLのように操作できるJavaScriptライブラリが無いか調べてみたところ、AlaSQLというものがあることを知りました。

調べてみると・・・結構良さそう！

AlaSQLをうまく使用することで、データベースやテーブルを用意しなくてもJavaScriptだけで完結でき、SQLのようにJSONデータを扱えるようになるようでした。

そこで今回は、このAlaSQLについて、簡単な使い方をご紹介します。

AlaSQL.jsとは

AlaSQLは、ブラウザやNode.jsで動く、軽量クライアントサイドのインメモリSQLデータベースです。

AlaSQLの概要

AlaSQLはピュアJavaScriptで書かれていて、ブラウザーのWebSQLデータベースを利用していません。
AlaSQLは完全な関数型のコンパクトなSQLサーバーで、JOINs, GROUPs, UNIONs, ANY, ALL, IN, といった句やサブクエリ、また、非常に限定的なトランザクションをサポートしています。
AlaSQLは、ROLLUP(), CUBE(), GROUPING SETS()といった関数をサポートしています。
AlaSQLはChrome, Firefox, IE, SafariといったモダンなブラウザやNode.js、モバイルのiOSやAndroidでも動きます。
AlaSQLはいくつかの最適化方法によって速いです。

使い方は簡単で、次のようにAlaSQL.jsを読み込めばOKです。

AlaSQLについては、こちらからダウンロードできます。

AlaSQL.jsで扱う値の定義方法

まず、SQLで操作したいデータを、次のような配列＆JSONで定義します。

var data = [ 
    { num: 1, hello: 'Hello!'   }
,   { num: 2, hello: 'Aloha!'   }
,   { num: 3, hello: 'Bonjour!' }
];

配列の各要素のなかに、JSONで「カラム名 : 値」となるように定義します。上記の例だと、次のようなテーブルと同じイメージとなります

テーブル：data
カラム：num、hello
レコード数：3レコード（data.length）

AlaSQLでの条件指定をした検索例（単一テーブル）

では、このデータを使って「numが1より大きい」データを抽出したい場合、次のようなコードを書くと実現できます。

var data = [ 
    { num: 1, hello: 'Hello!'   }
,   { num: 2, hello: 'Aloha!'   }
,   { num: 3, hello: 'Bonjour!' }
];
var sql = 'SELECT  *            ' +
          'FROM    ? dt         ' +
          'WHERE   1 = 1        ' +
          'AND     dt.num > ?   ';
var rs = alasql(sql, [data, 1]);
console.log(rs);

結果は次のようになり、条件に一致したデータが返されます。

この例では条件として1つのみ指定していますが、複数条件も指定可能です。

オブジェクトをループで回して条件分岐して・・・、といった事をせずに、SQLで欲しい結果を取得できているのが分かるかと思います。

ソースの解説ですが、まず「var sql = ・・・　」の部分ではSQLを変数として定義しています。SQLを書く際のポイントは次の通りです。

AlaSQLでのSQL定義時のポイント

テーブルはバインド変数（ ? ）で定義する。（SQL実行時にオブジェクトを割り当てます）

テーブル名に別名を指定可能

カラム名に別名を指定可能

次に「var rs = ・・・　」の部分SQLを実行しています。SQL実行結果がrsに格納されます。

SQLの実行は alasql(SQL文, 配列); 形式となっています。

alasql関数の第二引数には、SQL内で設定したバインド変数（?）にマッピングする変数を設定します。

この例では、SQLの最初のバインド変数にはテーブルとする変数を、2つ目のバインド変数には条件としての1を定義しています。

この例でのバインド変数へのマッピング

1つ目のバインド変数：テーブルとして使用するオブジェクトの変数

2つ目のバインド変数：1(条件として渡している値)

このようにJavaScriptのオブジェクトをSQLのように操作できますので、ロジックでゴリゴリ書くよりもスッキリとさせることが可能です。

AlaSQLでのグループ化で集計をした例（単一テーブル）

単純な条件指定以外に、AlaSQLでは集計関数にも対応をしています。

例えば先ほどの例について、「numの合計を取りたい」というような場合、次のようにします。

var data = [ 
    { num: 1, hello: 'Hello!'   }
,   { num: 2, hello: 'Aloha!'   }
,   { num: 3, hello: 'Bonjour!' }
];
var sql = 'SELECT  SUM(num) as cum ' +
          'FROM    ? dt             ' +
          'WHERE   1 = 1            ';
var rs = alasql(sql, [data]);
console.log(rs);

結果は次の通りで、合計値の6が結果として返されています。

これもSQLに慣れている人であればイメージがつくかと思いますが、とても簡単に合計値の集計が出来ることがお分かりになると思います。

条件を指定すればさらに複雑な集計も行う事ができますし、GROUP BY句ももちろん使用できます。

AlaSQLでの複数テーブルを結合した検索例

AlaSQLでは複数テーブルを結合して実行したSQLの結果を取得する事も可能です。

次の例は、aaaテーブルとbbbテーブルを、numカラムをキーに結合した結果を返す例です。

var aaa = [ 
    { num: 1, duedate : '2019-01-01', hello: 'Hello!'   }
,   { num: 2, duedate : '2019-02-01', hello: 'Aloha!'   }
,   { num: 3, duedate : '2019-03-01', hello: 'Bonjour!' }
,   { num: 4, duedate : '2019-03-01', hello: 'こんにちは!' }
];
var bbb = [ 
    { num: 1, hello: ''   }
,   { num: 4, hello: ''   }
];
var sql = 'SELECT t1.duedate  as COL_1   ' +
          '     , t1.hello    as COL_2   ' +
          '     , NOW()       as NOWDATE ' +
          'FROM   ? t1                   ' +
          '     , ? t2                   ' +
          'WHERE  1 = 1                  ' +
          'AND    t1.num = t2.num        '
var rs1 = alasql(sql, [aaa, bbb]);
console.log(rs1);

この実行結果としては次のようになり、SQLで実行したとおりの結果が返されています。

この例の中にあるNOW()は、AlaSQLで事前に定義されている関数です。他にも色々と用意されていますが、これについてはまた別の機会にご紹介したいと思います。

AlaSQLでの日付項目に対する集計の注意点

日付用の項目に対して最大値を取得したいような場合、少し注意が必要です。

例えば次のようなデータでSQLの実行をした場合、正しく日付の最大値を取得できるのはcol6のように設定した場合のみです。

var data = [ 
    { num: 1, numstr : "1", duedate : '2019-01-01', hello: 'Hello!'   }
,   { num: 2, numstr : "2", duedate : '2019-02-01', hello: 'Aloha!'   }
,   { num: 3, numstr : "3", duedate : '2019-03-01', hello: 'Bonjour!' }
,   { num: 4, numstr : "4", duedate : '2019-03-01', hello: 'こんにちは!' }
];
var sql = 'SELECT MAX(dt.num)                       as col1 ' +
          '     , MAX(dt.numstr)                    as col2 ' +
          '     , MAX(dt.hello)                     as col3 ' +
          '     , MAX(dt.duedate)                   as col4 ' +
          '     , MAX(DATE(dt.duedate))             as col5 ' +
          '     , dateFormat(MAX(DATE(dt.duedate))) as col6 ' +
          'FROM   ? dt                                      ' +
          'WHERE  1 = 1                                     ';
alasql.fn.dateFormat = function(datenum) {
    return moment(datenum).format('YYYY-MM-DD');
};
var rs3 = alasql(sql, [data]);
console.log(rs3);

結果は次の通りです。

特にcol4、col5、col6を見てください。

ここでは日付項目（duedate）に対してMAXを取得していますが、duedateの中に設定されている値は日付形式の文字列（YYYY-MM-DD形式）であるため、col4のように単純にMAX(duedate)では取得が出来ません。

そこでcol5ではMAX(DATE(dt.duedate)) のようにして一度文字列を日付に変えてからMAXを取っていますが、これだとミリ秒が返ってくるので、欲しい形ではありません。

最終的にはcol6で行っているように、SQL内でJavaScript関数が使用できるように定義しています。

alasql.fn.dateFormat = function(datenum) {
    return moment(datenum).format('YYYY-MM-DD');
};

上記は、dateFormatという関数をAlaSQL内で使えるようにするために定義したものです。この関数内ではmoment.jsを用いて日付文字列に変換した値を返しています。

このように、日付に関してはOracleやSQL ServerのようにMAXで取得できないため、少し注意が必要となります。

まとめ

JavaScriptでSQLライクにオブジェクトを扱えるAlaSQLについてご紹介しました。

うまく活用することでコーディング量を大幅に減らせると思いますので、是非お試しください。