在 SQL 中使用運算子

如果您想要跟隨範例，請參閱執行查詢的範例資料以檢視範例資料，並瞭解如何使用 OCI 主控台建立範例表格，並使用 JSON 檔案載入資料。

相關主題

順序比較運算子

兩個序列之間的比較是透過一組運算子來完成：=any、！=any、>any、>=any、<any、<=any。兩個輸入序列 S1 和 S2 上任何運算子的結果為真 (如果且只有有一組項目 i1 和 i2，其中 i1 屬於 S1、i2 屬於 S2，而 i1 和 i2 則透過對應的值比較運算子比較 true)。否則，如果任何輸入順序包含 NULL，則結果為 NULL。否則，結果為 false。

範例 1：尋找估計抵達時間大於 2019-03-01T13：00:00Z 之所有行李的乘客姓名與標籤編號。
SELECT fullname, bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].estimatedArrival
FROM BaggageInfo bag 
WHERE bag.bagInfo[].flightLegs[].estimatedArrival >any "2019-03-01T13:00:00Z"
說明：擷取預估抵達時間大於指定值之所有乘客包的全名與標記編號。">" 運算子 (bag.bagInfo[].flightLegs[].estimatedArrival) 左邊的運算元就是一系列的值。如果您嘗試使用一般比較運算子而非序列運算子，則會收到如下所示的錯誤。這就是您在此需要序列運算子的原因。
SELECT fullname, bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].estimatedArrival 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].estimatedArrival > "2019-03-01T13:00:00Z"
輸出顯示錯誤：
Error handling command SELECT fullname, bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].estimatedArrival
FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].estimatedArrival > "2019-03-01T13:00:00Z":
Error: at (1, 107) The left operand of comparison operator > is a sequence with more than one items.
Comparison operators cannot operate on sequences of more than one items.
輸出 (使用順序運算子後)：
{"fullname":"Lucinda Beckman","tagNum":"17657806240001","estimatedArrival":["2019-03-12T16:00:00Z","2019-03-13T03:14:00Z","2019-03-12T15:12:00Z"]}
{"fullname":"Elane Lemons","tagNum":"1765780623244","estimatedArrival":["2019-03-15T09:00:00Z","2019-03-15T10:14:00Z","2019-03-15T10:14:00Z"]}
{"fullname":"Dierdre Amador","tagNum":"17657806240229","estimatedArrival":"2019-03-07T14:00:00Z"}
{"fullname":"Henry Jenkins","tagNum":"17657806216554","estimatedArrival":["2019-03-02T09:00:00Z","2019-03-02T13:24:00Z"]}
{"fullname":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"estimatedArrival":["2019-03-12T16:00:00Z","2019-03-13T03:14:00Z",
"2019-03-12T15:12:00Z","2019-03-12T16:40:00Z","2019-03-13T03:18:00Z","2019-03-12T15:12:00Z"]}
{"fullname":"Gerard Greene","tagNum":"1765780626568","estimatedArrival":["2019-03-07T17:00:00Z","2019-03-08T04:10:00Z","2019-03-07T16:10:00Z"]}
{"fullname":"Doris Martin","tagNum":"17657806232501","estimatedArrival":["2019-03-22T09:00:00Z","2019-03-21T23:24:00Z","2019-03-22T10:24:00Z"]}
{"fullname":"Omar Harvey","tagNum":"17657806234185","estimatedArrival":["2019-03-02T02:00:00Z","2019-03-02T16:21:00Z"]}
{"fullname":"Mary Watson","tagNum":"17657806299833","estimatedArrival":["2019-03-13T15:00:00Z","2019-03-14T06:22:00Z"]}
{"fullname":"Kendal Biddle","tagNum":"17657806296887","estimatedArrival":["2019-03-04T22:00:00Z","2019-03-05T12:02:00Z"]}
範例 2：尋找從 JFK/ 透過 JFK 飛往任何其他位置的乘客人數。
SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "JFK"

說明：擷取航班來源為 JFK 的乘客標記編號，或經由 JFK 旅行的乘客。目的地可以是任何項目。

輸出：
{"tagNum":"17657806240229","fltRouteSrc":"JFK"}
{"tagNum":"17657806215913","fltRouteSrc":["JFK","IST"]}
{"tagNum":"17657806296887","fltRouteSrc":["JFK","IST"]}

邏輯運算子

運算子 AND 與 OR 為二進位且 NOT 運算子為一元。邏輯運算子的運算元是條件表示式，其類型必須為 BOOLEAN。運算元的空白結果會被視為假值。如果運算元傳回 NULL (SQL NULL 或 JSON NULL)，則：
  • AND 運算子會在其他運算元傳回 false 時傳回 false；否則傳回 NULL。
  • 如果其他運算元傳回 true，則 OR 運算子會傳回 true；否則會傳回 NULL。
  • NOT 運算子傳回 NULL。
範例 1：選取車票號碼為 1762311547917 或確認號碼為 KN4D1L 之旅的乘客及其行李的詳細資料。
SELECT fullName,bag.ticketNo, bag.confNo, 
bag.bagInfo[].tagNum, bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE bag.ticketNo=1762311547917 OR bag.confNo="KN4D1L"

說明：擷取符合兩個過濾條件之一的乘客明細。您可以使用 OR 子句來執行此操作。您可以擷取符合特定機票號碼或特定預訂代碼 (confNo) 的乘客全名、機票號碼、機票號碼、預訂代碼及路線詳細資料。

輸出：
{"fullName":"Rosalia Triplett","ticketNo":1762311547917,"confNo":"FH7G1W","tagNum":"17657806215913","routing":"JFK/IST/VIE"}
{"fullName":"Mary Watson","ticketNo":1762340683564,"confNo":"KN4D1L","tagNum":"17657806299833","routing":"YYZ/HKG/BLR"}
範例 2：選取旅客在 MIA 與 MEL 之間的行李明細。
SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc =any "MIA" AND
bag.bagInfo[].flightLegs[].fltRouteDest=any "MEL"

說明：您可以擷取在 MIA 與 MEL 之間旅行的乘客明細。由於您需要在此比對兩個條件，因此航班來源和航班目的地都使用 AND 運算子。在這裡，航班來源可以是航班的起點或任何運輸機場。同樣地，航班目的地可以是運輸機場或最終目的地。

輸出： 
{"fullName":"Zulema Martindale","tagNum":"17657806288937","routing":"MIA/LAX/MEL"}
{"fullName":"Adam Phillips","tagNum":"17657806255240","routing":"MIA/LAX/MEL"}
{"fullName":"Joanne Diaz","tagNum":"17657806292518","routing":"MIA/LAX/MEL"}
{"fullName":"Zina Christenson","tagNum":"17657806228676","routing":"MIA/LAX/MEL"}
範例 3：選取不源自 MIA/ 通過 MIA 的袋子明細。
SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
bag.bagInfo[].flightLegs[].fltRouteSrc
FROM BaggageInfo bag 
WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA"

說明：擷取不是源自特定來源之乘客的明細。若要擷取這些詳細資訊，請在此處使用 NOT 運算子。您想要提取未開始 / 通過 MIA 的行李明細。

輸出： 
{"fullName":"Kendal Biddle","tagNum":"17657806296887","routing":"JFK/IST/VIE","fltRouteSrc":"JFK"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Adelaide Willard","tagNum":"17657806224224","routing":"GRU/ORD/SEA","fltRouteSrc":"GRU"}
{"fullName":"Raymond Griffin","tagNum":"17657806243578","routing":"MSQ/FRA/HKG","fltRouteSrc":"MSQ"}
{"fullName":"Elane Lemons","tagNum":"1765780623244","routing":"MXP/CDG/SLC/BZN","fltRouteSrc":"MXP"}
{"fullName":"Dierdre Amador","tagNum":"17657806240229","routing":"JFK/MAD","fltRouteSrc":"JFK"}
{"fullName":"Henry Jenkins","tagNum":"17657806216554","routing":"SFO/ORD/FRA","fltRouteSrc":"SFO"}
{"fullName":"Rosalia Triplett","tagNum":"17657806215913","routing":"JFK/IST/VIE","fltRouteSrc":"JFK"}
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"],"fltRouteSrc":["SFO","SFO"]}
{"fullName":"Gerard Greene","tagNum":"1765780626568","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Doris Martin","tagNum":"17657806232501","routing":"BZN/SEA/CDG/MXP","fltRouteSrc":"BZN"}
{"fullName":"Omar Harvey","tagNum":"17657806234185","routing":"MEL/LAX/MIA","fltRouteSrc":"MEL"}
{"fullName":"Fallon Clements","tagNum":"17657806255507","routing":"MXP/CDG/SLC/BZN","fltRouteSrc":"MXP"}
{"fullName":"Lisbeth Wampler","tagNum":"17657806292229","routing":"LAX/TPE/SGN","fltRouteSrc":"LAX"}
{"fullName":"Teena Colley","tagNum":"17657806255823","routing":"MSQ/FRA/HKG","fltRouteSrc":"MSQ"}
{"fullName":"Michelle Payne","tagNum":"17657806247861","routing":"SFO/IST/ATH/JTR","fltRouteSrc":"SFO"}
{"fullName":"Mary Watson","tagNum":"17657806299833","routing":"YYZ/HKG/BLR","fltRouteSrc":"YYZ"}

NULL 運算子

IS NULL 運算子會測試其輸入表示式 (SQL 表示式或 JSON 物件) 的結果是否為空值。如果輸入表示式傳回多個項目，則會發生錯誤。如果輸入表示式的結果是空的，則 IS NULL 會傳回 false。否則，只有在輸入表示式計算的單一項目為空值時，IS NULL 才會傳回 true。IS NOT NULL 運算子等於 NOT (IS NULL cond_expr)。

範例 1：提取行李明細可用且非 NULL 之乘客的機票編號。
SELECT ticketNo,fullname FROM BaggageInfo bagdet 
WHERE bagdet.bagInfo is NOT NULL
說明：擷取有行李的乘客明細，表示 bagInfo JSON 不是空值。
輸出：
{"ticketNo":1762357254392,"fullname":"Teena Colley"}
{"ticketNo":1762330498104,"fullname":"Michelle Payne"}
{"ticketNo":1762340683564,"fullname":"Mary Watson"}
{"ticketNo":1762377974281,"fullname":"Kendal Biddle"}
{"ticketNo":1762320569757,"fullname":"Lucinda Beckman"}
{"ticketNo":1762392135540,"fullname":"Adelaide Willard"}
{"ticketNo":1762399766476,"fullname":"Raymond Griffin"}
{"ticketNo":1762324912391,"fullname":"Elane Lemons"}
{"ticketNo":1762390789239,"fullname":"Zina Christenson"}
{"ticketNo":1762340579411,"fullname":"Zulema Martindale"}
{"ticketNo":1762376407826,"fullname":"Dierdre Amador"}
{"ticketNo":176234463813,"fullname":"Henry Jenkins"}
{"ticketNo":1762311547917,"fullname":"Rosalia Triplett"}
{"ticketNo":1762320369957,"fullname":"Lorenzo Phil"}
{"ticketNo":1762341772625,"fullname":"Gerard Greene"}
{"ticketNo":1762344493810,"fullname":"Adam Phillips"}
{"ticketNo":1762355527825,"fullname":"Doris Martin"}
{"ticketNo":1762383911861,"fullname":"Joanne Diaz"}
{"ticketNo":1762348904343,"fullname":"Omar Harvey"}
{"ticketNo":1762350390409,"fullname":"Fallon Clements"}
{"ticketNo":1762355854464,"fullname":"Lisbeth Wampler"}
範例 2：提取沒有行李明細或 IS NULL 之乘客的機票號碼
SELECT ticketNo, fullname FROM BaggageInfo bagdet 
WHERE bagdet.bagInfo is NULL
0 row returned

值比較運算子

值比較運算子主要用於比較 2 個值，一個由左運算元產生，另一個由右運算元產生。如果有任何運算元傳回一個以上的項目，就會發生錯誤。如果兩個運算元都傳回空序列，則運算元會被視為相等 (如果運算子為 =、<= 或 >=，則會傳回真)。如果只有一個運算元傳回空白，則比較的結果為 false，除非運算子為 ！=。如果運算元傳回 NULL，則比較表示式的結果也是 NULL。否則，便會產生布林值。

範例 1：選取所有男性乘客的全名與遞送。
SELECT fullname, bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender="M"

說明：資料會根據性別進行篩選。值比較運算子 "=" 用於篩選資料。

輸出：
{"fullname":"Lucinda Beckman","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adelaide Willard","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","routing":"MIA/LAX/MEL"}
{"fullname":"Dierdre Amador","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","routing":"JFK/MAD"}
{"fullname":"Lorenzo Phil","routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"fullname":"Gerard Greene","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adam Phillips","routing":"MIA/LAX/MEL"}
{"fullname":"Fallon Clements","routing":"MXP/CDG/SLC/BZN"}
{"fullname":"Lisbeth Wampler","routing":"LAX/TPE/SGN"}
{"fullname":"Teena Colley","routing":"MSQ/FRA/HKG"}
您可以使用 "!=" 比較運算子重寫此查詢。若要取得所有男性乘客的詳細資料，您的查詢可以篩選性別不是「F」的資料。這只有在假設資料欄性別中只能有兩個值為 "F" 與 "M" 時才有效。
SELECT fullname,bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender!="F";
範例 2：擷取機票號碼大於 176236000000 的乘客姓名與路線詳細資料。
SELECT fullname, ticketNo, 
bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE ticketNo > 1762360000000

說明：您需要車票號碼大於指定值的乘客明細。您可以使用 ">" 運算子來篩選資料。

輸出：
{"fullname":"Adelaide Willard","ticketNo":1762392135540,"tagNum":"17657806224224","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","ticketNo":1762399766476,"tagNum":17657806243578,"routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","ticketNo":1762390789239,"tagNum":"17657806228676","routing":"MIA/LAX/MEL"}
{"fullname":"Bonnie Williams","ticketNo":1762397286805,"tagNum":"17657806216554","routing":"SFO/ORD/FRA"}
{"fullname":"Joanne Diaz","ticketNo":1762383911861,"tagNum":"17657806292518","routing":"MIA/LAX/MEL"}
{"fullname":"Kendal Biddle","ticketNo":1762377974281,"tagNum":"17657806296887","routing":"JFK/IST/VIE"}
{"fullname":"Dierdre Amador","ticketNo":1762376407826,"tagNum":"17657806240229","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","ticketNo":1762392196147,"tagNum":"17657806240229","routing":"JFK/MAD"}
範例 3：選取源自證券及期貨條例 / 轉運至證券及期貨條例的所有行李標籤編號。
SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"
說明：您可以提取源自證券及期貨條例或通過證券及期貨條例的行李編號。雖然您使用值比較運算子 = ，因為 flightLegs 是陣列，比較運算子 = 的左運算元是具有多個項目的順序。這是除了值比較運算子 = 之外，順序運算子 any 的使用原因。否則會發生下列錯誤。
Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc= "SFO": 
Error: at (3, 6) The left operand of comparison operator = is a sequence with more than one items. 
Comparison operators cannot operate on sequences of more than one items.
輸出： 
{"tagNum":"17657806240001","fltRouteSrc":"SFO"}
{"tagNum":"17657806216554","fltRouteSrc":"SFO"}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","SFO"]}
{"tagNum":"1765780626568","fltRouteSrc":"SFO"}
{"tagNum":"17657806247861","fltRouteSrc":"SFO"}
範例 4：選取不是源自 JFK 的所有行李標籤編號。
SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!=ANY "JFK"
說明：此處的假設是 flightLegs 陣列的第一筆記錄具有來源位置的詳細資訊。您可以在此處使用 ！= 運算子擷取未源自 JFK 的行李標記編號。雖然您使用值比較運算子 ！= ，因為 flightLegs 是陣列，比較運算子 ！= 的左運算元是具有多個項目的順序。這是除了值比較運算子 ！= 之外，順序運算子 any 的使用原因。否則會發生下列錯誤。
Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!="JFK": 
Failed to display result set: Error: at (2, 0) The left operand of comparison operator != is a sequence with 
more than one items. Comparison operators cannot operate on sequences of more than one items.
輸出：
{"tagNum":"17657806240001","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806224224","fltRouteSrc":["GRU","ORD"]}
{"tagNum":"17657806243578","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"1765780623244","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806228676","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806234185","fltRouteSrc":["MEL","LAX"]}
{"tagNum":"17657806255507","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806292229","fltRouteSrc":["LAX","TPE"]}
{"tagNum":"17657806255823","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"17657806247861","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806299833","fltRouteSrc":["YYZ","HKG"]}
{"tagNum":"17657806288937","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806216554","fltRouteSrc":["SFO","ORD"]}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","IST","ATH","SFO","IST","ATH"]}
{"tagNum":"1765780626568","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806232501","fltRouteSrc":["BZN","SEA","CDG"]}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"]}

IN 運算子

IN 運算子基本上是一些 OR 型相等條件的精簡替代方案。此運算子可讓您在 WHERE 子句中指定多個值。

範例：擷取客戶 "Lucinda Beckman", "Adam Phillips","Zina Christenson","Fallon Clements" 的標記編號。
SELECT bagdet.fullName, bagdet.bagInfo[].tagNum 
FROM BaggageInfo bagdet 
WHERE bagdet.fullName IN 
("Lucinda Beckman", "Adam Phillips","Zina Christenson","Fallon Clements")

說明：擷取乘客清單的標記編號。要擷取的乘客清單可在 IN 子句內提供。

輸出：
{"fullName":"Lucinda Beckman","tagNum":"17657806240001"}
{"fullName":"Zina Christenson","tagNum":"17657806228676"}
{"fullName":"Adam Phillips","tagNum":"17657806255240"}
{"fullName":"Fallon Clements","tagNum":"17657806255507"}

正規表示式條件

正規表示式是正規表示式引擎嘗試與輸入字串比對的樣式。regex_like 函數會執行正規表示式比對。regex_like 函數提供的功能與標準 SQL 中的 LIKE 運算子類似，亦即，可用來檢查輸入字串是否符合指定的樣式。輸入字串和樣式分別由第一個和第二個引數計算。第三個 (選擇性) 引數指定一組旗標，這些旗標會影響比對的完成方式。

樣式字串是與輸入文字相符的正規表示式。period (.) 是中介字元，會比對新行以外的每個字元。貪婪式數量詞 (*) 是中介字元，表示前一個元素的零次或多次出現。例如，正規表示式 "D.*" 會比對以 'D' 字元為開頭且後面跟著零或多個字元的任何字串。

範例 1：擷取以 'Z' 開頭之乘客的行李資訊。
SELECT bag.fullname,bag.bagInfo[].tagNum 
FROM BaggageInfo bag
WHERE regex_like(fullName, "Z.*")

說明：擷取全名以 Z 開頭之乘客的全名與標記編號。您可以使用正規表示式，並指定完整名稱中的第一個字元應為 " Z "，而其餘字元則可為其他字元。

輸出：
{"fullname":"Zina Christenson","tagNum":"17657806228676"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937"}

範例 2：擷取其航班來源地點為 " M " 之乘客的行李資訊。

選項 1：
SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag 
WHERE regex_like(bag.bagInfo.flightLegs[0].fltRouteSrc, ".*M.*")

說明：此處的假設是 flightLegs 陣列的第一筆記錄具有來源位置的詳細資訊。您可以擷取航班來源中包含 " M " 的乘客全名和標籤號碼。您可以使用正規表示式，指定來源欄位中的其中一個字元應該是 " M "，而其餘字元可以是其他字元。

您也可以使用不同的方法來撰寫查詢，以解決上述的問題。

選項 2：使用 regex_like 函數來決定正確的索引，而不是硬式編碼 flightLegs 陣列的索引。
SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE EXISTS (bag.bagInfo.flightLegs[regex_like($element.fltRouteSrc, ".*M.*")])
選項 3：您可以使用「路由」欄位的子字串來擷取來源，然後使用 regex_like 函數來搜尋來源中的字母 M 。
SELECT bag.fullname,bag.bagInfo[].tagNum,
substring(bag.bagInfo[].routing,0,3) 
FROM BaggageInfo bag WHERE
regex_like(substring(bag.bagInfo[].routing,0,3), ".*M.*")
輸出： 
{"fullname":"Raymond Griffin","tagNum":"17657806243578","fltRouteSrc":"MSQ"}
{"fullname":"Elane Lemons","tagNum":"1765780623244","fltRouteSrc":"MXP"}
{"fullname":"Zina Christenson","tagNum":"17657806228676","fltRouteSrc":"MIA"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937","fltRouteSrc":"MIA"}
{"fullname":"Adam Phillips","tagNum":"17657806255240","fltRouteSrc":"MIA"}
{"fullname":"Joanne Diaz","tagNum":"17657806292518","fltRouteSrc":"MIA"}
{"fullname":"Teena Colley","tagNum":"17657806255823","fltRouteSrc":"MSQ"}
{"fullname":"Omar Harvey","tagNum":"17657806234185","fltRouteSrc":"MEL"}
{"fullname":"Fallon Clements","tagNum":"17657806255507","fltRouteSrc":"MXP"}

EXISTS 運算子

EXISTS 運算子會檢查其輸入表示式傳回的順序是否空白，並分別傳回 false 或 true。特別是在輸入表示式傳回 NULL 時。在此情況下，EXISTS 也會傳回 NULL。

範例 1：選取具有三個航段之乘客的乘客明細與行李資訊。
SELECT fullName, bag.bagInfo[].tagNum, 
bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE EXISTS bag.bagInfo[].flightLegs[2]

說明：擷取具有三個航段之乘客的明細。您可以使用 EXISTS 運算子來評估飛行航段陣列的第三個元素是否存在。

輸出：
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"fullName":"Gerard Greene","tagNum":"1765780626568","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Doris Martin","tagNum":"17657806232501","routing":"BZN/SEA/CDG/MXP"}
{"fullName":"Fallon Clements","tagNum":"17657806255507","routing":"MXP/CDG/SLC/BZN"}
{"fullName":"Michelle Payne","tagNum":"17657806247861","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","routing":"SFO/IST/ATH/JTR"}
{"fullName":"Elane Lemons","tagNum":"1765780623244","routing":"MXP/CDG/SLC/BZN"}
範例 2：擷取 2019 年之後出貨之所有客戶行李的完整名稱與標記編號。 
SELECT fullName, bag.ticketNo 
FROM BaggageInfo bag WHERE
EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

說明：每個行李的行李抵達日期值應大於 2019 年。在此，「$element」會繫結至相關資訊環境資料列 (客戶的每個包袋)。EXISTS 運算子會檢查其輸入表示式傳回的順序是否空白。比較運算子 ">=" 傳回的序列對 2019 年之後到達的所有行李而言不是空的。

輸出： 
{"fullName":"Lucinda Beckman","ticketNo":1762320569757}
{"fullName":"Adelaide Willard","ticketNo":1762392135540}
{"fullName":"Raymond Griffin","ticketNo":1762399766476}
{"fullName":"Elane Lemons","ticketNo":1762324912391}
{"fullName":"Zina Christenson","ticketNo":1762390789239}
{"fullName":"Zulema Martindale","ticketNo":1762340579411}
{"fullName":"Dierdre Amador","ticketNo":1762376407826}
{"fullName":"Henry Jenkins","ticketNo":176234463813}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957}
{"fullName":"Gerard Greene","ticketNo":1762341772625}
{"fullName":"Adam Phillips","ticketNo":1762344493810}
{"fullName":"Doris Martin","ticketNo":1762355527825}
{"fullName":"Joanne Diaz","ticketNo":1762383911861}
{"fullName":"Omar Harvey","ticketNo":1762348904343}
{"fullName":"Fallon Clements","ticketNo":1762350390409}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464}
{"fullName":"Teena Colley","ticketNo":1762357254392}
{"fullName":"Michelle Payne","ticketNo":1762330498104}
{"fullName":"Mary Watson","ticketNo":1762340683564}
{"fullName":"Kendal Biddle","ticketNo":1762377974281}

Is-Of-Type 運算子

is-of-type 運算子會根據一或多個目標序列類型，檢查其輸入序列的序列類型。如果目標類型的數字 N 大於 1，則表示式等同於 OR-ing N 類型的表示式，每個表示式都有一個目標類型。

範例：提取行李標記僅包含數字而非 STRING 的乘客姓名。
SELECT fullname,bag.bagInfo.tagNum 
FROM  BaggageInfo bag 
WHERE bag.bagInfo.tagNum is of type (NUMBER)

說明：bagInfo 綱要中的 tagNum 是 STRING 資料類型。但應用程式可能會誤以 NUMBER 值作為 tagNum。查詢會擷取 tagNum 資料欄只有數字的乘客。

輸出：
{"fullname":"Raymond Griffin","tagNum":17657806243578}
如果您查詢上述 tagNum 的 bagInfo 綱要作為 STRING，則不會顯示任何資料列。
SELECT * FROM BaggageInfo bag WHERE tagnum = "17657806232501"
0 row returned
您也可以提取行李標籤僅包含 STRING 的乘客姓名。
SELECT fullname,bag.bagInfo.tagNum 
FROM BaggageInfo bag 
WHERE  bag.bagInfo.tagNum is of type (STRING)

使用 QueryRequest API 的 SQL 運算子範例

您可以使用 QueryRequest API，並使用 SQL 運算子從 NoSQL 表格篩選資料。

若要執行查詢，請使用 NoSQLHandle.query() API。

Download the full code SQLOperators.java from the examples here.
 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
} 

String seq_comp_ope="SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
System.out.println("Using Sequence Comparison operator ");
fetchRows(handle,seq_comp_ope);
String logical_ope="SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
System.out.println("Using Logical operator ");
fetchRows(handle,logical_ope);
String value_comp_ope="SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=\"M\"";
System.out.println("Using Value Comparison operator ");
fetchRows(handle,value_comp_ope);
String in_ope="SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet WHERE bagdet.fullName IN (\"Lucinda Beckman\", \"Adam Phillips\",\"Dierdre Amador\",\"Fallon Clements\")";System.out.println("Using IN operator ");fetchRows(handle,in_ope);
String exists_ope="SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE EXISTS bag.bagInfo[$element.bagArrivalDate >=\"2019-03-01T00:00:00\"]";
System.out.println("Using EXISTS operator ");
fetchRows(handle,exists_ope);

若要執行查詢，請使用 borneo.NoSQLHandle.query() 方法。

Download the full code SQLOperators.py from the examples here.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))  
seqcomp_stmt = '''SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                     FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
print('Using Sequence Comparison operator:')
fetch_data(handle,seqcomp_stmt)
logope_stmt = '''SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                 bag.bagInfo[].flightLegs[].fltRouteSrc
                 FROM BaggageInfo bag
                 WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
print('Using Logical operator:')
fetch_data(handle,logope_stmt)
valcomp_stmt = '''SELECT fullname, bag.bagInfo[].routing
                  FROM BaggageInfo bag WHERE gender="M"'''
print('Using Value Comparison operator:')
fetch_data(handle,valcomp_stmt)
inope_stmt = '''SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                FROM BaggageInfo bagdet WHERE bagdet.fullName IN
               ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")'''
print('Using IN operator:')
fetch_data(handle,inope_stmt)
existsope_stmt = '''SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                    EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]'''
print('Using EXISTS operator:')
fetch_data(handle,existsope_stmt)

若要執行查詢，請使用 Client.Query 函數。

Download the full code SQLOperators.go from the examples here.
 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
} 
seqcomp_stmt := `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                    FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
fmt.Printf("Using Sequence Comparison operator:\n"
fetchData(client, err,tableName,seqcomp_stmt)

logope_stmt := `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                bag.bagInfo[].flightLegs[].fltRouteSrc
                FROM BaggageInfo bag
                WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
fmt.Printf("Using Logical operator:\n")
fetchData(client, err,tableName,logope_stmt)

valcomp_stmt := `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
fmt.Printf("Using Value Comparison operator:\n")
fetchData(client, err,tableName,valcomp_stmt)

inope_stmt := `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet
	        WHERE bagdet.fullName IN ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
fmt.Printf("Using IN operator:\n")
fetchData(client, err,tableName,inope_stmt)

existsope_stmt := `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                   EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
fmt.Printf("Using EXISTS operator:\n")
fetchData(client, err,tableName,existsope_stmt)

若要執行查詢，請使用 query 方法。

JavaScript: Download the full code SQLOperators.js from the examples here.
  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
TypeScript: Download the full code SQLOperators.ts from the examples here.
interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const seqcomp_stmt = `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                      FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
console.log("Using Sequence Comparison operator");
await fetchData(handle,seqcomp_stmt);

const logope_stmt = `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                     bag.bagInfo[].flightLegs[].fltRouteSrc
                     FROM BaggageInfo bag
                     WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
console.log("Using Logical operator");
await fetchData(handle,logope_stmt);

const valcomp_stmt = `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
console.log("Using Value Comparison operator");
await fetchData(handle,valcomp_stmt);

const inope_stmt = `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                    FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                    ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
console.log("Using IN operator");
await fetchData(handle,inope_stmt);

const existsope_stmt = `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                        EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
console.log("Using EXISTS operator");
await fetchData(handle,existsope_stmt);

若要執行查詢，您可以呼叫 QueryAsync 方法或呼叫 GetQueryAsyncEnumerable 方法，然後重複產生的非同步可列舉。

Download the full code SQLOperators.cs from the examples here.
private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
} 
private const string seqcomp_stmt =@"SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                                           FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
Console.WriteLine("\nUsing Sequence Comparison operator!");
await fetchData(client,seqcomp_stmt);

private const string logope_stmt =@"SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                                           bag.bagInfo[].flightLegs[].fltRouteSrc
                                           FROM BaggageInfo bag
                                           WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
Console.WriteLine("\nUsing Logical operator!");
await fetchData(client,logope_stmt);

private const string valcomp_stmt =@"SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=""M""" ;
Console.WriteLine("\nUsing Value Comparison operator!");
await fetchData(client,valcomp_stmt);

private const string inope_stmt =@"SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                                          FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                                          (""Lucinda Beckman"", ""Adam Phillips"",""Dierdre Amador"",""Fallon Clements"")";
Console.WriteLine("\nUsing IN operator!");
await fetchData(client,inope_stmt);

private const string existsope_stmt =@"SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                                             EXISTS bag.bagInfo[$element.bagArrivalDate >=""2019-03-01T00:00:00""]";
Console.WriteLine("\nUsing EXISTS operator!");
await fetchData(client,existsope_stmt);