クエリ言語リファレンス

このガイドでは、Docwize Query Builder 向けの JSON ベースのクエリの書き方を説明します。以下の DSL（ドメイン固有言語）ルールに従ってクエリを構築してください。システムはこれを SQL に変換します。

クエリの構造

クエリは節（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`	SQL の `LIKE '%…%'`	`{ "contains":[{"var":"Email"},"@docwize.com"] }`
`in`	`column IN (サブクエリ)`	`{ "in":[ tblSpec, {"select":[...]} ] }`
`groupby`	GROUP BY	`{ "groupby":[ colSpec, ... ] }`
`aggregations`	サマリー関数（sum、count など）	`{ "aggregations":[ {"alias":"total","aggregator":"sum"} ] }`
`order_by`	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",     // ホワイトリストに登録されている必要があります
    "args": [
      { "keyword": "DAY" },
      { "var": "Table.Date1" },
      { "func": {"name": "GETDATE", "args": []} }
    ],
    "label": "days_diff"
  }
}

name: 使用可能な関数一覧（以下参照）に含まれる関数名。
args: 任意の有効な仕様（var、ネストされた func、リテラル、算術演算）の配列。
label: 結果列のオプションエイリアス。

4. リテラルキーワード（`keyword`）

素の SQL トークン（例: 日付部分、キャスティングキーワード）に使用します:

{ "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 → SQL の column LIKE '%' || :param || '%'

in → サブクエリは select 節である必要があります。例:

{
  "in": [
    { "var": "Users.id" },
    { "select": [ { "var": "AllowedUsers.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"} ]
}

ビルダーは内部クエリをサブクエリとしてラップし、.group_by(...) と集計呼び出しを適用します。

結果の並び替え

{ "order_by": [ {"alias":"count_by_status","desc":true} ] }

alias は選択または集計された列と一致する必要があります。
desc: 降順の場合は true、昇順の場合は false（または省略）。

使用可能な関数

以下は "func" 仕様で呼び出せる関数です。関数名は大文字・小文字を区別しませんが、通常は大文字で記述します。このリストにない関数は拒否されます。

MSSQL 日付・時刻: GETDATE, GETUTCDATE, DATEADD, DATEDIFF, DATEPART, CURRENT_TIMESTAMP, DATENAME, DAY, MONTH, SYSDATETIME, SYSUTCDATETIME, TIMEFROMPARTS, TODATETIMEOFFSET, YEAR

MSSQL 文字列: ASCII, CHAR, CHARINDEX, CONCAT, FORMAT, LEFT, LEN, LOWER, LTRIM, NCHAR, PATINDEX, QUOTENAME, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, STR, STUFF, SUBSTRING, TRANSLATE, TRIM, UNICODE, UPPER

MSSQL 数値: ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, TAN

MSSQL 集計: AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, MAX, MIN, STDEV, STDEVP, SUM, VAR, VARP

MSSQL 論理: CHOOSE, COALESCE, IIF, ISNULL, NULLIF

MSSQL JSON: JSON_VALUE, JSON_QUERY, JSON_MODIFY, ISJSON, OPENJSON

MSSQL ウィンドウ・ランキング: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK

MSSQL 暗号化: HASHBYTES

MSSQL テキスト検索: CONTAINS, LIKE

MSSQL システム（安全）: CAST, CONVERT, PARSE, TRY_CAST, TRY_CONVERT, TRY_PARSE

MSSQL XML: FOR XML, NODES, QUERY, VALUE

MSSQL 空間: STArea, STAsText, STBuffer, STDistance, STIntersects, STLength, STOverlaps

PostgreSQL 日付・時刻: NOW, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, AGE, TO_TIMESTAMP, TO_DATE, DATE_PART, DATE_TRUNC

PostgreSQL 文字列: CHAR_LENGTH, LOWER, UPPER, INITCAP, CONCAT, POSITION, SUBSTRING, TRIM, OVERLAY, REPLACE, TO_CHAR, TO_NUMBER, SPLIT_PART

PostgreSQL 数値: ABS, CEIL, FLOOR, ROUND, POWER, MOD, SQRT, EXP, LN, LOG, RADIANS, DEGREES, PI, RANDOM, SIGN, TRUNC, COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2

PostgreSQL 集計: AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE

PostgreSQL JSON: JSON_BUILD_OBJECT, JSONB_BUILD_OBJECT, JSON_ARRAY_LENGTH, JSONB_ARRAY_LENGTH, JSON_EXTRACT_PATH_TEXT, TO_JSON, TO_JSONB

PostgreSQL 論理: COALESCE, NULLIF, CASE, GREATEST, LEAST

PostgreSQL ウィンドウ: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE

PostgreSQL テキスト検索: TO_TSVECTOR, TO_TSQUERY, PLAINTO_TSQUERY, PHRASETO_TSQUERY, WEBSEARCH_TO_TSQUERY, TS_RANK, TS_RANK_CD

Docwize DW: FNGETPERIOD, FNGETPERIOD_WEEK

使用可能なキーワード

"keyword" 仕様で認識されるリテラル（大文字・小文字を区別しない）:

一般的なキャスト/データ型: CAST, CONVERT, AS

文字/文字列型: CHAR, CHARACTER, VARCHAR, TEXT, NCHAR, NVARCHAR

数値型: DECIMAL, NUMERIC, INT, INTEGER, BIGINT, SMALLINT, FLOAT, REAL, DOUBLE

日付/時刻型: DATE, TIME, DATETIME, TIMESTAMP, INTERVAL, ZONE

ブール: BOOLEAN, TRUE, FALSE

バイナリ/BLOB: BINARY, VARBINARY, BYTEA

JSON: JSON, JSONB

配列: ARRAY

PostgreSQL 固有: UUID, TSVECTOR, TSQUERY, CIDR, INET, MACADDR, BIT, VARBIT, XML, GEOMETRY

トリミング: LOWER, UPPER, TRIM, TRAILING, LEADING

Null: NULL

DATEDIFF または EXTRACT の日付部分: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND

ヒントと注意事項

仮想ビュー: {} を使用して id または name で JSON 定義のビューを取り込む。
エイリアスの重複: 同じソースを複数回結合する場合は、必ずエイリアスを割り当てる。
パラメーターバインディング: リテラル値はバインドパラメーター（:Param_1）になる。
オプションフィルター: 入力がない場合にフィルターをスキップするには optional_runtime_input を使用する。
バリデーション: サポートされていない関数/キーワードは明確なパース時エラーを生成する。

クエリでの関数の呼び出し

データベース関数（例: 日付計算、文字列操作）を使用するには、JSON DSL に func 節を含めます。これによりビルダーは式を対応する SQL 関数でラップします。

{
  "func": {
    "name": "<FUNCTION_NAME>",   // 「使用可能な関数」リストの関数と一致する必要があります
    "args": [ <arg1>, <arg2>, ... ],  // 引数: リテラル、キーワード、列参照、またはネストされた関数
    "label": "alias"             // （オプション）結果列のエイリアス
  }
}

name: 使用可能な関数に記載されている通りの関数名（大文字・小文字を区別しない）。
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"
  }
}

CASE
  WHEN "Users"."status" = :status_1 THEN "Users"."login_count"
  WHEN "Users"."status" = :status_2 THEN 0
END AS login_when_active

2. 日付差: `DATEDIFF` と `GETDATE`

注文作成からの経過日数を計算する:

{
  "func": {
    "name": "DATEDIFF",
    "args": [
      { "keyword": "DAY" },
      { "var": "Orders.created_at" },
      { "func": { "name": "GETDATE", "args": [] } }
    ],
    "label": "age_in_days"
  }
}

DATEDIFF(DAY, "Orders"."created_at", GETDATE()) AS age_in_days

3. 文字列操作: `CONCAT` と `LOWER`

小文字のフルネームを構築する:

{
  "func": {
    "name": "CONCAT",
    "args": [
      { "func": { "name": "LOWER", "args": [ { "var": "Users.firstname" } ] } },
      " ",
      { "var": "Users.lastname" }
    ],
    "label": "full_name"
  }
}

CONCAT(LOWER("Users"."firstname"), ' ', "Users"."lastname") AS full_name

4. 数値の丸め: `ABS` と `ROUND`

小数点以下2桁に丸めた絶対値:

{
  "func": {
    "name": "ROUND",
    "args": [
      { "func": { "name": "ABS", "args": [ { "var": "Transactions.amount" } ] } },
      2
    ],
    "label": "abs_amount_rounded"
  }
}

ROUND(ABS("Transactions"."amount"), 2) AS abs_amount_rounded

5. 集計: `SUM` と `COUNT`

カテゴリ別の商品数をカウントする:

{
  "select": [
    { "var": "Products.category" },
    {
      "func": {
        "name": "COUNT",
        "args": [ { "var": "Products.id" } ],
        "label": "product_count"
      }
    }
  ],
  "groupby": [ { "var": "Products.category" } ]
}

SELECT "Products"."category",
       COUNT("Products"."id") AS product_count
FROM "Products"
GROUP BY "Products"."category";

6. ネストされた関数

ある関数の結果を別の関数の引数として使用する:

{
  "func": {
    "name": "DATEADD",
    "args": [
      { "keyword": "MONTH" },
      1,
      {
        "func": {
          "name": "DATEDIFF",
          "args": [
            { "keyword": "DAY" },
            { "var": "Subscriptions.start_date" },
            { "func": { "name": "CURRENT_TIMESTAMP", "args": [] } }
          ]
        }
      }
    ],
    "label": "next_billing"
  }
}

DATEADD(MONTH, 1,
        DATEDIFF(DAY, "Subscriptions"."start_date", CURRENT_TIMESTAMP)
) AS next_billing

name、args、オプションの label を指定するこのパターンにより、ホワイトリストに登録された関数（シンプルなものでもネストされたものでも）を JSON クエリ内で直接活用し、豊かで動的な SQL 式を構築できます。

クエリの構造​

仮想 JSON ビューの参照​

トップレベルの節​

列と式の仕様​

1. 列参照（var）​

エイリアスまたはラベル付き​

2. var 内の仮想ビュー参照​

3. 関数呼び出し（func）​

4. リテラルキーワード（keyword）​

5. 算術演算（operation）​

6. ランタイム入力​

ブール演算子と比較節​

グループ化と集計​

結果の並び替え​

使用可能な関数​

使用可能なキーワード​

ヒントと注意事項​

クエリでの関数の呼び出し​

関数使用例​

1. CASE による条件ロジック​

2. 日付差: DATEDIFF と GETDATE​

3. 文字列操作: CONCAT と LOWER​

4. 数値の丸め: ABS と ROUND​

5. 集計: SUM と COUNT​

6. ネストされた関数​