メインコンテンツまでスキップ

クエリ言語リファレンス

このガイドでは、Docwize Query Builder 向けの JSON ベースのクエリの書き方を説明します。以下のルールでクエリの形式を説明します。

クエリの構造​

クエリは節(clauses)の JSON 配列です。節は以下の順序で処理されます:

  1. from: データソース(テーブル、ビュー、または仮想ビュー)を指定する
  2. join/left_join/outer_join: 追加のテーブルまたはビューを関連付ける
  3. select: 返すフィールドまたは式を列挙する
  4. where スタイルのフィルター(and、or、比較演算子)
  5. groupby + aggregations: グループ化とサマリー関数
  6. 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"] }
incolumn 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 タブにある関数をクエリから呼び出せます。