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

2017鐵人賽 azure documentdb

yowko

2016-12-28 02:01:59

2235 瀏覽

分享至

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

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

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

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

DocumentDB SQL 特性

如同 JOSN 一樣的樹狀結構，可以讀取一層一層的節點
動態繫結類型系統 - 結構化查詢語言( SQL ) 查詢無結構化描述資料( JSON )，不保證會是固定結構型式
只支援嚴謹的 JSON 格式，規格可以看 JSON 規格
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 值；但如果是無效的，就會額外加上做為字串值的原因。