在 SQL 中使用主表达式
如果要随示例一起操作,请参阅用于运行查询的示例数据以查看示例数据,并了解如何使用 OCI 控制台创建示例表并使用 JSON 文件加载数据。
带括号的表达式
带括号的表达式主要用于更改运算符之间的默认优先级。它们还用作句法辅助,以其他方式混合表达式,否则会导致句法模糊。
SELECT fullName, bag.bagInfo.tagNum,
bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest
FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "JFK" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "MAD" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "VIE" )
解释:要提取乘客的全名、标记编号和路由详细信息。第一个过滤条件是登机点/中转为 JFK。满足此条件后,第二个过滤条件是目标为 MAD 或 VIE。可以使用 OR 条件筛选目标值。
{"fullName":"Dierdre Amador","tagNum":"17657806240229","routing":"JFK/MAD","fltRouteDest":"MAD"}
{"fullName":"Rosalia Triplett","tagNum":"17657806215913","routing":"JFK/IST/VIE","fltRouteDest":["IST","VIE"]}
{"fullName":"Kendal Biddle","tagNum":"17657806296887","routing":"JFK/IST/VIE","fltRouteDest":["IST","VIE"]}
Case 表达式
搜索的 CASE 表达式类似于传统编程语言的 if-then-else 语句。它由多个 WHEN-THEN 对组成,后跟可选的 ELSE 子句。每个 WHEN 表达式都是一个条件,即必须返回 BOOLEAN。THEN 表达式和 ELSE 表达式可以返回任何项目序列。对 CASE 表达式求值时,首先从上到下对 WHEN 表达式求值,直到返回 true 的第一个表达式。如果 i-th WHEN 表达式返回 true,则计算 i-th THEN 表达式,其结果是整个 CASE 表达式的结果。如果没有 WHEN 表达式返回 true,则存在 ELSE,对其表达式求值,其结果是整个 CASE 表达式的结果;否则,CASE 表达式的结果是空序列。
SELECT
fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762340683564
解释:您希望使用 CASE 语句确定乘客 bagInfo
的转运次数。如果 flightLegs
数组没有元素,则乘客没有包数据。如果 flightLegs
数组只有一个元素,则只有一个传输点。同样,如果 flightLegs
数组具有两个元素,则有两个跃点。另外还有三个过境点。在这里,你假设一个包最多可以有三个过境点/跃点。
{"fullName":"Mary Watson","NUMBER_HOPS":"you have two hops."}
tagNum
。SELECT bag.bagInfo[].tagNum,
CASE
WHEN bag.bagInfo[0].tagNum is of type (NUMBER)
THEN "Tagnumber is not a STRING. Update the data"
ELSE "Tagnumber has correct datatype"
END AS tag_NUM_TYPE
FROM BaggageInfo bag
解释:bagInfo
方案中乘客的 tagNum
是 STRING 数据类型。但应用程序可能会错误地将 NUMBER 值作为 tagNum
的值。查询使用“属于类型”运算符捕获此值,如果现有值不是字符串,则提示系统更新 tagNum
。
{"tagNum":"17657806240001","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":"17657806224224","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":17657806243578,"tag_NUM_TYPE":"Tagnumber is not a STRING. Update the data"}
{"tagNum":"1765780623244","tag_NUM_TYPE":"Tagnumber has correct datatype"}
强制表达式
如果可能,强制表达式会根据输入序列的项创建给定目标类型的新项。例如,可以使用 CAST 表达式将 STRING 转换为 TIMESTAMP(0)。
- 如果输入项的类型等于目标项类型,则强制转换为无操作:将返回输入项本身。
- 如果目标类型为 JSON 之外的通配符类型,并且输入项的类型是通配符类型的子类型,则强制转换为 no-op。
- 如果目标类型为 JSON,则如果输入项为非 json 原子类型,则会引发错误。
- 如果目标类型为数组类型,则如果输入项不是数组,则会引发错误。
- 如果目标类型为字符串,则输入项可以是任何类型。这意味着每个项目都可以转换为字符串。对于时间戳,其字符串值为 UTC,格式为
uuuu-MM-dd['T'HH:mm:ss]
。 - 如果目标类型为字符串以外的原子类型,则输入项也必须是原子。
-
- 可以将整数和长整型转换为时间戳。输入值解释为自 1970 年 1 月 1 日格林尼治时间 00:00:00 以来的毫秒数。
- 字符串项可能适用于所有其他原子类型。强制转换是否成功取决于是否可以将实际字符串值解析为属于目标类型的域的值。
- 时间戳项适用于所有时间戳类型。如果目标类型的精度小于输入项的精度,则生成的时间戳是与目标精度中的输入时间戳最接近的时间戳。
-
- 要将 STRING 转换为 TIMESTAMP,如果输入具有 ISO-8601 格式的 STRING 值,则 SQL 运行时会自动将其转换为 TIMESTAMP 数据类型。
注意:
ISO8601 介绍了表示日期、时间和工期的一种国际公认方式。Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
其中- YYYY 将年份指定为 4 位小数
- MM 将月份指定为两位小数,00 到 12
- DD 将日期指定为两位小数,00 到 31
- hh 将小时指定为两位小数,00 至 23
- mm 指定分钟,以两位小数表示,00 到 59
- ss[.s[s[s[s]]]] 指定以两位小数(00 到 59)后跟小数点和 1 到 6 位小数(表示秒的小数部分)表示的秒数。
- Z 指定 UTC 时间(时区 0)。(也可以通过 +00:00 指定,但不能通过 -00:00 指定。)
- (+|-)hh:mm 指定时区与 UTC 的区别。(需要 + 或 - 之一。)
SELECT CAST (bag.bagInfo.bagArrivalDate AS Timestamp(3))
AS BAG_ARRIVING_DATE
FROM BaggageInfo bag WHERE bag.confNo=DN3I4Q
解释:bagArrivalDate
是 STRING。使用 CAST 可以将此字段转换为 TIMESTAMP 格式。
{"BAG_ARRIVING_DATE":"2019-02-15T21:21:00.000Z"}
SELECT fullName, bag.ticketNo,
bag.bagInfo[].bagArrivalDate
FROM BaggageInfo bag WHERE
exists bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]
解释:您希望筛选和显示 2019 年以后发运的行李的详细信息。将 flightLegs
数组中每个元素的包到达日期与给定时间戳 (2019-01-01T00:00:00
) 进行比较。此处强制转换是隐式的,因为 bagArrivalDate
是一个 STRING,直接与静态时间戳值进行比较。当可以执行隐式强制转换时,不需要显式 CAST 函数。但是,您的数据格式应为 YYYY-MM-DDTHH:MI:SS
。然后,使用 EXISTS 条件检查是否存在此时间戳条件的 bagInfo。
{"fullName":"Kendal Biddle","ticketNo":1762377974281,"bagArrivalDate":"2019-03-05T12:00:00Z"}
{"fullName":"Lucinda Beckman","ticketNo":1762320569757,"bagArrivalDate":"2019-03-12T15:05:00Z"}
{"fullName":"Adelaide Willard","ticketNo":1762392135540,"bagArrivalDate":"2019-02-15T21:21:00Z"}
{"fullName":"Raymond Griffin","ticketNo":1762399766476,"bagArrivalDate":"2019-02-03T08:09:00Z"}
{"fullName":"Elane Lemons","ticketNo":1762324912391,"bagArrivalDate":"2019-03-15T10:13:00Z"}
{"fullName":"Zina Christenson","ticketNo":1762390789239,"bagArrivalDate":"2019-02-04T10:08:00Z"}
{"fullName":"Zulema Martindale","ticketNo":1762340579411,"bagArrivalDate":"2019-02-25T20:15:00Z"}
{"fullName":"Dierdre Amador","ticketNo":1762376407826,"bagArrivalDate":"2019-03-07T13:51:00Z"}
{"fullName":"Henry Jenkins","ticketNo":176234463813,"bagArrivalDate":"2019-03-02T13:18:00Z"}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917,"bagArrivalDate":"2019-02-12T07:04:00Z"}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957,"bagArrivalDate":["2019-03-12T15:05:00Z","2019-03-12T16:25:00Z"]}
{"fullName":"Gerard Greene","ticketNo":1762341772625,"bagArrivalDate":"2019-03-07T16:01:00Z"}
{"fullName":"Adam Phillips","ticketNo":1762344493810,"bagArrivalDate":"2019-02-01T16:13:00Z"}
{"fullName":"Doris Martin","ticketNo":1762355527825,"bagArrivalDate":"2019-03-22T10:17:00Z"}
{"fullName":"Joanne Diaz","ticketNo":1762383911861,"bagArrivalDate":"2019-02-16T16:13:00Z"}
{"fullName":"Teena Colley","ticketNo":1762357254392,"bagArrivalDate":"2019-02-13T11:15:00Z"}
{"fullName":"Michelle Payne","ticketNo":1762330498104,"bagArrivalDate":"2019-02-02T23:59:00Z"}
{"fullName":"Mary Watson","ticketNo":1762340683564,"bagArrivalDate":"2019-03-14T06:22:00Z"}
{"fullName":"Omar Harvey","ticketNo":1762348904343,"bagArrivalDate":"2019-03-02T16:09:00Z"}
{"fullName":"Fallon Clements","ticketNo":1762350390409,"bagArrivalDate":"2019-02-21T14:08:00Z"}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464,"bagArrivalDate":"2019-02-10T10:01:00Z"}
序列转换表达式
序列转换表达式可将序列转换为另一个序列。从语法上看,它看起来像一个名为 seq_transform
的函数。第一个参数是生成要转换的序列(输入序列)的表达式,第二个参数是针对输入序列的每个项计算的“映射器”表达式。seq_transform
表达式的结果是映射器表达式的每个求值生成的序列的串联。映射器表达式可以通过 $ 变量访问当前输入项。
ticketNo
,提取包含对该 ticketNo
的行李执行的所有操作的平面数组。SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions
FROM baggageInfo l WHERE ticketNo=1762340683564
解释:可以使用序列转换表达式转换表行中存储的 JSON 文档。在这种情况下,您通常会使用相互嵌套的多个序列转换表达式。此处,可能需要内部序列转换的映射器表达式来访问外部序列转换的当前项。为此,每个序列转换表达式 'S' 声明一个名为 $sqN
的变量,其中 N 是外部序列转换表达式中表达式 S
的嵌套级别。$sqN
基本上是 $
的同义词,即绑定到输入表达式 S
返回的项。但是,其他可能嵌套在表达式 S
中的序列转换表达式可以访问 $sqN
。
{
"actions":[
{"action":"ONLOAD to HKG","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"BagTag Scan at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"Checkin at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"Offload to Carousel at BLR","at":"BLR","flightNo":"BM115","tagNum":"17657806299833"},
{"action":"ONLOAD to BLR","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"},
{"action":"OFFLOAD from HKG","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"}
]
}
使用 QueryRequest API 的 SQL 表达式示例
可以使用 QueryRequest
API 并使用 SQL 表达式筛选 NoSQL 表中的数据。
要执行查询,请使用 NoSQLHandle.query()
API。
//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 paran_expr="SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing, "+
"bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag WHERE "+
"bag.bagInfo.flightLegs[].fltRouteSrc=any \"SFO\" AND "+
"(bag.bagInfo[].flightLegs[].fltRouteDest=any \"ATH\" OR "+
"bag.bagInfo[].flightLegs[].fltRouteDest=any \"JTR\" )";
System.out.println("Using Paranthesized expression ");
fetchRows(handle,paran_expr);
String case_expr="SELECT fullName,"+
"CASE WHEN NOT exists bag.bagInfo.flightLegs[0] "+
"THEN \"you have no bag info\" "+
"WHEN NOT exists bag.bagInfo.flightLegs[1] "+
"THEN \"you have one hop\" "+
"WHEN NOT exists bag.bagInfo.flightLegs[2] "+
"THEN \"you have two hops.\" "+
"ELSE \"you have three hops.\" "+
"END AS NUMBER_HOPS "+
"FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using Case Expression ");
fetchRows(handle,case_expr);
String seq_trn_expr="SELECT seq_transform(l.bagInfo[],"+
"seq_transform("+
"$sq1.flightLegs[],"+
"seq_transform("+
"$sq2.actions[],"+
"{"+
"\"at\" : $sq3.actionAt,"+
"\"action\" : $sq3.actionCode,"+
"\"flightNo\" : $sq2.flightNo,"+
"\"tagNum\" : $sq1.tagNum"+
"}"+
")"+
")"+
") AS actions FROM baggageInfo l WHERE ticketNo=1762376407826";
System.out.println("Using Sequence Transform Expressions ");
fetchRows(handle,seq_trn_expr);
要执行查询,请使用 borneo.NoSQLHandle.query()
方法。
# 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))
paran_expr = '''SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )'''
print('Using Paranthesized expression:')
fetch_data(handle,paran_expr)
case_expr = '''SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using Case Expression:')
fetch_data(handle,case_expr)
seq_trn_expr = '''SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826'''
print('Using Sequence Transform Expressions:')
fetch_data(handle,seq_trn_expr)
要执行查询,请使用 Client.Query
函数。
//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()))
}
}
paran_expr := `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
fmt.Printf("Using Paranthesized expression:\n")
fetchData(client, err,tableName,paran_expr)
case_expr := `SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using Case Expression:\n")
fetchData(client, err,tableName,case_expr)
seq_trn_expr := `SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
fmt.Printf("Using Sequence Transform Expressions:\n")
fetchData(client, err,tableName,seq_trn_expr)
要执行查询,请使用 query
方法。
//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);
}
}
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: any) {
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 paran_expr = `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
console.log("Using Paranthesized expression");
await fetchData(handle,paran_expr);
const case_expr = `SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using Case Expression");
await fetchData(handle,case_expr);
const seq_trn_expr = `SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
console.log("Using Sequence Transform Expressions");
await fetchData(handle,seq_trn_expr);
要执行查询,可以调用 QueryAsync
方法或调用 GetQueryAsyncEnumerable
方法并迭代生成的异步可枚举。
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 paran_expr =@"SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any ""SFO"" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any ""ATH"" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any ""JTR"" )";
Console.WriteLine("\nUsing Paranthesized expression:!");
await fetchData(client,paran_expr);
private const string case_expr =@"SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN ""you have no bag info""
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN ""you have one hop""
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN ""you have two hops.""
ELSE ""you have three hops.""
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing Case Expression!");
await fetchData(client,case_expr);
private const string seq_trn_expr =@"SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
""at"" : $sq3.actionAt,
""action"" : $sq3.actionCode,
""flightNo"" : $sq2.flightNo,
""tagNum"" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826" ;
Console.WriteLine("\nUsing Sequence Transform Expressions!");
await fetchData(client,seq_trn_expr);