Azure DocumentDB - Part 7 (DocumentDB 中的 SQL 語法 2)

Microsoft Azure DocumentDB 可以讓開發人員使用熟悉的 SQL 語法來查詢 json 文件，大大降低了開發上的進入門檻

1. 支援 SQL 查詢，而不是發明新的 JSON 查詢語法
2. DocumentDB 身為一個 JSON document databse，可以直接在 database 中執行 Javascript,而 DocumentDB SQL 則是以 JavaScript 的 type system, expression evaluation, 及 function 叫用為基礎.

在有語法基礎後，接著來更深入地了解 DocumentDB 中的 SQL 語法中與 SQL 比較不同的地方

DocumentDB SQL 特性

1. 如同 JOSN 一樣的樹狀結構，可以讀取一層一層的節點
2. 動態繫結類型系統 - 結構化查詢語言( SQL ) 查詢 無結構化描述資料( JSON )，不保證會是固定結構型式
3. 只支援嚴謹的 JSON 格式，規格可以看 JSON 規格
4. Document 間(每個 JSON 間)的關聯是使用 內嵌資料(一種反正規化模型) 來處理，不是 primary key 跟 foreign key，關於內嵌資料可以看 在 DocumentDB 中模型化資料

DocumentDB SQL 基礎

• 查詢是以唯讀形式執行，可以在主要或次要複本上執行
• 只有 SELECT 是必要的，其他皆可省略
  SELECT [TOP <top_expression>] <select_list>
  [FROM <from_specification>]
  [WHERE <filter_condition>]
  [ORDER BY <sort_specification]

1. 迭代查詢( Iteration )

• 可以將不同物件的子物件輸出為單一陣列, 讓物件扁平化
  • SELECT * FROM Families.children

       [
            [
              {
                "firstName": "Henriette Thaulow"
              }
            ],
            [
              {
                  "familyName": "Merriam",
              },
              {
                  "familyName": "Miller",
              }
            ]
          ] ```
    
    

• SELECT * FROM c IN Families.children
  [ { "firstName": "Henriette Thaulow" }, { "familyName": "Merriam" }, { "familyName": "Miller" } ]

2. Joins

• DocumentDB 無結構描述，使用反正規化資料模型
• 將 join 的物件交叉計算而來

    SELECT 
         f.id AS familyName,
         c.givenName AS childGivenName,
         c.firstName AS childFirstName,
         p.givenName AS petName 
  	FROM Families f 
  	JOIN c IN f.children 
  	JOIN p IN c.pets
  

• 行為類似巢狀迴圈

  for-each(Family f in Families)
  	{    
        for-each(Child c in f.children)
        {
            for-each(Pet p in c.pets)
            {
                return (Tuple(f.id AS familyName, 
                  c.givenName AS childGivenName, 
                  c.firstName AS childFirstName,
                  p.givenName AS petName));
            }
        }
    }
  

3. JavaScript 整合

• User Defined Functions(UDF)
  • 開發人員依需要傳入參數，經處理後得到一個值的結果 (純量值函數)
  • UDF 無法存取 Stored Procedure 或是 Trigger
  • UDF 是設計成在次要複本上執行,查詢是以唯讀形式執行，可以在主要或次要複本上執行
  • 呼叫時, 需加上前綴 udf.
  • 定義方式

     UserDefinedFunction seaLevelUdf = new UserDefinedFunction()
    {
       Id = "SEALEVEL",
       Body = @"function(city) {
               switch (city) {
                   case 'seattle':
                       return 520;
                   case 'NY':
                       return 410;
                   case 'Chicago':
                       return 673;
                   default:
                       return -1;
                }"
        };
    
    UserDefinedFunction createdUdf = await client.CreateUserDefinedFunctionAsync(UriFactory.CreateDocumentCollectionUri("testdb", "families"),seaLevelUdf);
    

  • 使用方式

    SELECT f.address.city, udf.SEALEVEL(f.address.city) AS seaLevel FROM Families f
    

4. 參數化 SQL 查詢

• 可避免 SQL Injection
• 使用 @ 來標記參數
• 參數值可以是任可有效 JSON (字串、數字、布林值、null，陣列或巢狀 JSON)

     {      
         "query": "SELECT TOP @n * FROM Families",     
         "parameters": [          
             {"name": "@n", "value": 10},         
         ] 
     }
  

5. 內建函數

• 數學函數

  函數名稱	說明

  ABS (num_expr)| 取絕對值。
  CEILING (num_expr)| 取大於等於指定值的最小整數。
  FLOOR (num_expr)| 取小於等於指定值的最大整數。
  EXP (num_expr)| 取指定值的指數。
  LOG (num_expr [,base])| 取指定值的自然對數
  LOG10 (num_expr)| 取指定值以 10 為底的對數。
  ROUND (num_expr)| 取四捨五入值。
  TRUNC (num_expr)| 無條件捨去取整數值。
  SQRT (num_expr)| 取指定值的平方根。
  SQUARE (num_expr)| 取指定值的平方。
  POWER (num_expr, num_expr)| 取指定值的乘冪。
  SIGN (num_expr)| 取指定值的正負號值 (-1、0、1)。
  ACOS (num_expr)| 傳回角度，以弧度為單位，取反餘弦值。
  ASIN (num_expr)| 傳回角度，以弧度為單位，取反正弦值。
  ATAN (num_expr)| 傳回角度，以弧度為單位，取反正切值。
  ATN2 (num_expr)| 傳回角度，以弧度為單位，正 x 軸和從原點 (y、x) 點的切線之間，其中 x 和 y 是兩個指定之浮點運算式的值。
  COS (num_expr)| 在指定運算式中傳回指定角度的三角餘弦函數，以弧度為單位。
  COT (num_expr)| 在指定的數值運算式中傳回指定角度的三角餘切函數，以弧度為單位。
  DEGREES (num_expr)| 針對以弧度指定的角度，傳回對應的角度 (以度為單位)。
  PI ()| 傳回 PI 的常數值。
  RADIANS (num_expr)| 當您輸入數值運算式 (以度為單位) 時，傳回弧度。
  SIN (num_expr)| 在指定運算式中傳回指定角度的三角正弦函數 (以弧度為單位)。
  TAN (num_expr)| 在指定運算式中傳回輸入運算式的正切函數。

• 類型檢查函數

  函數名稱	說明

  IS_ARRAY (expr)| boolean，檢查是否為陣列。
  IS_BOOL (expr)| boolean，檢查是否為布林值。
  IS_NULL (expr)| boolean，檢查是否為 null。
  IS_NUMBER (expr)| boolean，檢查是否為數字。
  IS_OBJECT (expr)| boolean，檢查是否為 JSON 物件。
  IS_STRING (expr)| boolean，檢查是否為字串。
  IS_DEFINED (expr)| boolean，檢查是否已經指派值。
  IS_PRIMITIVE (expr)| boolean，檢查是否為字串、數字、布林值或 Null。

• 字串函數

  函數名稱	說明

  LENGTH (str_expr)| 取得的字元數量
  CONCAT (str_expr, str_expr [, str_expr])| 字串串接。
  SUBSTRING (str_expr, num_expr, num_expr)| 取字串的一部分。
  STARTSWITH (str_expr, str_expr)| 傳回布林值，檢查第一個字串是否以第二個字串做為開頭
  ENDSWITH (str_expr, str_expr)| 傳回布林值，檢查第一個字串是否以第二個結束字串做為含尾
  CONTAINS (str_expr, str_expr)| 傳回布林值，檢查第一個字串是否包含第二個字串。
  INDEX_OF (str_expr, str_expr)| 傳回第二個字串運算式第一次出現的開始位置，或者如果找不到，則為 -1。
  LEFT (str_expr, num_expr)| 傳回具有指定字元位置的字串左側部分。
  RIGHT (str_expr, num_expr)| 傳回具有指定字元位置的字串右側部分。
  LTRIM (str_expr)| 移除字串前面開頭的空白。
  RTRIM (str_expr)| 截斷字串所有結尾的空白。
  LOWER (str_expr)| 轉換成小寫。
  UPPER (str_expr)| 轉換成大寫。
  REPLACE (str_expr, str_expr, str_expr)| 將第一字串中的第二字串取代為第三字串
  REPLICATE (str_expr, num_expr)| 將字串值重複指定的次數。
  REVERSE (str_expr)| 字串反轉。

• 陣列函數

  函數名稱	說明

  ARRAY_LENGTH (arr_expr)| 傳回指定陣列運算式的元素數目。
  ARRAY_CONCAT (arr_expr, arr_expr [, arr_expr])| 串連兩個或多個陣列
  ARRAY_CONTAINS (arr_expr, expr)| 傳回布林值，表示陣列是否包含指定值。
  ARRAY_SLICE (arr_expr, num_expr [, num_expr])| 陣列分割。

• 空間函數

  函數名稱	說明

  ST_DISTANCE (point_expr、point_expr)| 傳回兩個 GeoJSON 點運算式之間的距離。
  ST_WITHIN (point_expr、polygon_expr)| 傳回布林運算式，指出第一個引數中指定的 GeoJSON 點是否位在第二個引數的 GeoJSON 多邊形內。
  ST_ISVALID| 傳回布林值，指出指定的 GeoJSON 點或多邊形運算式是否有效。
  ST_ISVALIDDETAILED| 如果指定的 GeoJSON 點或多邊形運算式是有效的，就會傳回包含布林值的 JSON 值；但如果是無效的，就會額外加上做為字串值的原因。

6. 彙總函式

暫無直接支援方式，暫時做法可以參考 彙總函式

參考資料

1. DocumentDB 中的 SQL 查詢和 SQL 語法