在 SQL 中使用运算符

如果要随示例一起操作，请参阅用于运行查询的示例数据以查看示例数据，并了解如何使用 OCI 控制台创建示例表并使用 JSON 文件加载数据。

相关主题

序列比较运算符

两个序列之间的比较是通过一组运算符完成的：=any、！=any、>any、>=any、<any、<=any。如果且仅当存在一对项 i1 和 i2，其中 i1 属于 S1，i2 属于 S2，i1 和 i2 通过相应的值比较运算符比较 true 时，两个输入序列 S1 和 S2 上的任何运算符的结果均为 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），则：
  • 如果其他操作数返回 false，则 AND 运算符返回 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 之间旅行的乘客的详细信息。由于您需要匹配此处的 2 个条件、航班源和航班目的地，因此您正在使用 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"

解释：提取不是来自特定来源的乘客的详细信息。要提取这些详细信息，您在此处使用不是运算符。您要提取未启动/通过 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 对象）的结果是否为 NULL。如果输入表达式返回多个项，则会引发错误。如果输入表达式的结果为空，IS NULL 将返回 false。否则，当且仅当输入表达式计算出的单个项为 NULL 时，IS NULL 将返回真。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 个值，一个由左操作数生成，另一个由右操作数生成。如果任何操作数返回多个项，则会引发错误。如果两个操作数都返回空序列，则操作数视为相等（如果运算符为 =、<= 或 >=，则返回 true）。如果只有一个操作数返回空，则除非运算符为 ！=，否则比较的结果为 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：选择源自 SFO/通过 SFO 传输的所有包标记编号。
SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"
解释：提取源自 SFO 或通过 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 运算符，也就是说，它可用于检查输入字符串是否与给定模式匹配。输入字符串和模式分别由第一个和第二个参数计算。第三个可选参数指定一组影响匹配方式的标志。

模式字符串是与输入文本相匹配的正则表达式。句点 (.) 是匹配除新行之外的每个字符的元字符。贪婪量词 (*) 是一个元字符，表示前一个元素出现次数为零或更多。例如，正则表达式 "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 运算符根据一个或多个目标序列类型检查其输入序列的序列类型。如果目标类型的 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);