クエリ言語リファレンス
このガイドでは、Docwize Query Builder 向けの JSON ベースのクエリの書き方を説明します。以下のルールでクエリの形式を説明します。
クエリの構造
クエリは節(clauses)の JSON 配列です。節は以下の順序で処理されます:
- from: データソース(テーブル、ビュー、または仮想ビュー)を指定する
- join/left_join/outer_join: 追加のテーブルまたはビューを関連付ける
- select: 返すフィールドまたは式を列挙する
- where スタイルのフィルター(
and、or、比較演算子) - groupby + aggregations: グループ化とサマリー関数
- order_by: 最終結果を並び替える
骨格:
[
{ "from": [ { "var": "TableOrView" } ] },
{ "join": [ tableSpec, onCondition ] },
{ "select": [ colSpec, ... ] },
{ "and": [ cond1, cond2 ] },
{ "groupby": [ ... ] },
{ "aggregations": [ ... ] },
{ "order_by": [ ... ] }
]
仮想 JSON ビューの参照
キャッシュされた「仮想ビュー」は、その 名前 または ID を波括弧で囲んで参照することでテーブルと同様に扱えます:
- ID による参照:
{42}はid = 42のビューを参照します。 - 名前による参照:
{MyViewName}(大文字・小文字を区別しない)は、nameが一致するビューを参照します。
from または join で参照すると、そのビューが出力するすべての列が select、フィルターなどで使用できるようになります。エイリアスを追加することもできます:
{ "from": [ { "var": "{42}", "alias": "v" } ] }
トップレベルの節
| 節 | 目的 | 例 |
|---|---|---|
from | ベースソース | { "from":[ {"var":"Users"} ] } または { "from":[ {"var":"{ReportsSummary}","alias":"r"} ] } |
join | 内部結合 | { "join":[ tblSpec, condSpec ] } |
left_join | 左(外部)結合 | { "left_join":[ tblSpec, condSpec ] } |
outer_join | 左外部結合(left_join のエイリアス) | { "outer_join":[ tblSpec, condSpec ] } |
select | 返す列/式 | { "select":[ colSpec, ... ] } |
and / or | フィルター条件の組み合わせ | { "and":[cond1,cond2] }、{ "or":[...] } |
| 比較演算子 | ==、!=、<、<=、>、>= | { "<":[{"var":"Amount"}, 1000] } |
contains | テキストを含む | { "contains":[{"var":"Email"},"@example.com"] } |
in | column IN (サブクエリ) | { "in":[ tblSpec, {"select":[...]} ] } |
groupby | 行をグループ化する | { "groupby":[ colSpec, ... ] } |
aggregations | サマリー関数(sum、count など) | { "aggregations":[ {"alias":"total","aggregator":"sum"} ] } |
order_by | 行を並び替える | { "order_by":[ {"alias":"total","desc":true} ] } |
ここで:
-
tblSpec は
selectスタイルのcolSpecと同一ですが、列を持たずテーブル/ビューを参照します。例:{ "var": "MyTable" } -
condSpec は任意のブール/比較/JSON 演算子節です(以下参照)。
列と式の仕様
1. 列参照(var)
テーブルまたはビュー(仮想ビューを含む)から列を取得する場合に使用します:
{ "var": "TableOrView.ColumnName" }
エイリアスまたはラベル付き
{ "var": [
"Table.Column", // テーブルまたはビュー名(ビューの場合は `{}` を使用)
{ "alias": "t" }, // テーブル/ビューのオプションエイリアス
{ "label": "col_alias" } // 列のオプションラベル
] }
2. var 内の仮想ビュー参照
{ "var": [
"{42}.MetricValue", // ビュー id 42 からの列
{ "alias": "mv" }, // オプションエイリアス
{ "label": "value" } // オプション列ラベル
] }
3. 関数呼び出し(func)
{
"func": {
"name": "DATEDIFF", // Functions タブにある関数
"args": [
{ "keyword": "DAY" },
{ "var": "Table.Date1" },
{ "func": {"name": "GETDATE", "args": []} }
],
"label": "days_diff"
}
}
- name: Query Lab の Functions タブにある関数名(関数を参照)。
- args: 任意の有効な仕様(
var、ネストされたfunc、リテラル、算術演算)の配列。 - label: 結果列のオプションエイリアス。
4. リテラルキーワード(keyword)
素のキーワード(例: 日付部分、データ型)に使用します:
{ "keyword": "DAY" }
{ "keyword": "VARCHAR" }
5. 算術演算(operation)
{
"operation": "+", // +、-、*、/ のいずれか
"left": {"var":"A.x"},
"right": {"var":"B.y"},
"label": "sum_xy" // オプションエイリアス
}
6. 実行時入力
実行時にユーザーが指定した値を取り込みます:
{ "runtime_input": "filter.min_date" }
{ "optional_runtime_input": ["filter","status"] }
- runtime_input: 値がない、または null の場合、条件全体がスキップされます。
- optional_runtime_input: 値がない場合、静かにスキップされます(中止しません)。
ブール演算子と比較節
これらは and/or 配列内にインラインまたは内部で使用されます:
{ "==": [ expr1, expr2 ] }
{ "!=" : [ expr1, expr2 ] }
{ ">": [ expr1, expr2 ] }
{ "<=": [ expr1, expr2 ] }
{ "contains": [ expr, substring ] }
{ "in": [ tblSpec, subquery ] }
-
contains→ 列にそのテキストが含まれます。 -
in→ サブクエリはselect節である必要があります。例:{"in": [{ "var": "Users.id" },{ "select": [ { "var": "Managers.id" } ] }]}
論理演算子との組み合わせ:
{ "and": [ cond1, cond2, ... ] }
{ "or": [ ... ] }
{ "not": cond }
グループ化と集計
groupby: グループ化する列/式の一覧。aggregations:{ alias, aggregator }オブジェクトの一覧。aggregatorは集計関数(例:SUM、COUNT)です。
例:
{
"groupby": [ {"var":"Orders.status"} ],
"aggregations": [ {"alias":"count_by_status","aggregator":"COUNT"} ]
}
結果の並び替え
{ "order_by": [ {"alias":"count_by_status","desc":true} ] }
- alias は選択または集計された列と一致する必要があります。
- desc: 降順の場合は
true、昇順の場合はfalse(または省略)。
関数
クエリから呼び出せる関数は、Query Lab の Functions タブに一覧表示されます。関数名は大文字・小文字を区別しません。サポートされていない関数は、ビューの保存時に拒否されます。
キーワード
"keyword" 仕様で認識されるリテラル(大文字・小文字を区別しない):
キャスト: CAST, CONVERT, AS
文字列型: CHAR, VARCHAR, TEXT
数値型: DECIMAL, NUMERIC, INT, INTEGER, BIGINT, FLOAT
日付/時刻型: DATE, TIME, TIMESTAMP
ブール: BOOLEAN, TRUE, FALSE
Null: NULL
日付部分: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND
ヒントと注意事項
- 仮想ビュー:
{}を使用してidまたはnameで JSON 定義のビューを取り込む。 - エイリアスの重複: 同じソースを複数回結合する場合は、必ずエイリアスを割り当てる。
- オプションフィルター: 入力がない場合にフィルターをスキップするには
optional_runtime_inputを使用する。 - バリデーション: サポートされていない関数/キーワードは、ビューの保存時に拒否される。
クエリでの関数の呼び出し
関数(例: 日付計算、文字列操作)を使用するには、クエリに func 節を含めます。
{
"func": {
"name": "<FUNCTION_NAME>", // Functions タブにある関数
"args": [ <arg1>, <arg2>, ... ], // 引数: リテラル、キーワード、列参照、またはネストされた関数
"label": "alias" // (オプション)結果列のエイリアス
}
}
-
name: Functions タブに記載されている通りの関数名(大文字・小文字を区別しない)。 -
args: 各要素が以下のいずれかである配列:- 列参照:
{ "var": "Table.Column" } - リテラル値: 例:
100、"text" - キーワード:
{ "keyword": "DAY" } - ネストされた関数: 別の
{ "func": {...} }オブジェクト
- 列参照:
-
label: (オプション)結果セット内の出力列名。
関数使用例
1. CASE による条件ロジック
ステータスが Active の場合は login_count を返し、Inactive の場合は 0 を返す:
{
"func": {
"name": "case",
"args": [
[
[ { "==": [ { "var": "Users.status" }, "Active" ] },
{ "var": "Users.login_count" }
],
[ { "==": [ { "var": "Users.status" }, "Inactive" ] },
0
]
]
],
"label": "login_when_active"
}
}
2. 文字列操作: CONCAT と LOWER
小文字のフルネームを構築する:
{
"func": {
"name": "CONCAT",
"args": [
{ "func": { "name": "LOWER", "args": [ { "var": "Users.firstname" } ] } },
" ",
{ "var": "Users.lastname" }
],
"label": "full_name"
}
}
3. 数値の丸め: ABS と ROUND
小数点以下2桁に丸めた絶対値:
{
"func": {
"name": "ROUND",
"args": [
{ "func": { "name": "ABS", "args": [ { "var": "Transactions.amount" } ] } },
2
],
"label": "abs_amount_rounded"
}
}
4. 集計: SUM と COUNT
カテゴリー別の商品数をカウントする:
{
"select": [
{ "var": "Products.category" },
{
"func": {
"name": "COUNT",
"args": [ { "var": "Products.id" } ],
"label": "product_count"
}
}
],
"groupby": [ { "var": "Products.category" } ]
}
name、args、オプションの label を指定するこのパターンにより、Functions タブにある関数をクエリから呼び出せます。