对父表使用 NESTED TABLES

JOIN 用于基于两个或多个表中的相关列组合行。在分层表中,子表继承其父表的主键列。这是隐式执行的,不会在子项的 CREATE TABLE 语句中包含父列。层次结构中的所有表都具有相同的分片键列。

可以使用 NESTED TABLES 子句联接 Oracle NoSQL Database 中的表。

NESTED TABLES 概览

NESTED TABLES 子句指定参与表并将其分为 3 个组。首先,指定从中提取数据的目标表。然后,ANCESTORS 子句(如果存在)指定在表层次结构中必须是目标表的祖先的表数。最后,DESCENDANTS 子句(如果存在)指定在表层次中必须是目标表的后代的表数。

注意:

从语义上讲,NESTED TABLES 子句等效于目标表周围的一些左外部联接操作“居中”。
NESTED 表的特征:
  • 查询同一层次结构中的多个表
  • 它不是 ANSI-SQL 标准
  • 它支持同级表联接

表 - 嵌套表与 LOJ

嵌套表 LOJ
查询同一层次结构中的多个表 查询同一层次结构中的多个表
不是 ANSI-SQL 标准 ANSI-SQL 标准
支持同级表联接 不支持同级表联接

如果要随示例一起执行操作,请下载脚本 parentchildtbls_loaddata.sql 并按如下所示运行该脚本。此脚本将创建示例中使用的表,并将数据加载到表中。

启动 KVSTORE 或 KVLite 并打开 SQL.shell。
 java -jar lib/kvstore.jar kvlite -secure-config disable
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
parentchildtbls_loaddata.sql 包含以下内容:
### Begin Script ###
load -file parentchild.ddl
import -table ticket  -file ticket.json
import -table ticket.bagInfo  -file bagInfo.json
import -table ticket.passengerInfo  -file passengerInfo.json
import -table ticket.bagInfo.flightLegs  -file flightLegs.json
### End Script ###
使用 load 命令运行该脚本。
load -file parentchildtbls_loaddata.sql
您可以使用 OCI 控制台创建示例中使用的表,并将数据加载到表中。在 OCI 控制台中,使用高级 DDL 输入模式使用下面给出的 DDL 语句创建以下表。
### CREATE table ticket if not present ###
CREATE TABLE IF NOT EXISTS ticket(ticketNo LONG, 
confNo STRING, 
PRIMARY KEY(ticketNo));

### CREATE table ticket.baginfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo(id LONG,tagNum LONG,routing STRING,
lastActionCode STRING,
lastActionDesc STRING,
lastSeenStation STRING,
lastSeenTimeGmt TIMESTAMP(4),
bagArrivalDate TIMESTAMP(4), PRIMARY KEY(id));

### CREATE table ticket.bagInfo.flightLegs if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo.flightLegs(flightNo STRING, 
flightDate TIMESTAMP(4),
fltRouteSrc STRING,
fltRouteDest STRING,
estimatedArrival TIMESTAMP(4), 
actions JSON, PRIMARY KEY(flightNo));

### CREATE table ticket.passengerInfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.passengerInfo(contactPhone STRING, 
fullName STRING,
gender STRING, PRIMARY KEY(contactPhone));

有关使用 DDL 语句创建表的步骤,请参见 Creating Singleton Table:Advanced DDL Input Mode

要将数据加载到从 OCI 控制台创建的表中,请单击表名。显示表的详细信息。单击上载数据。单击选择要上载的文件并提供要上载的 JSON 文件。您可以在此处下载父子表的 DDL 和 JSON 文件。

使用嵌套表的示例

示例中使用的各种表:
  • ticket
    ticketNo LONG
    confNo STRING
    PRIMARY KEY(ticketNo)
  • ticket.bagInfo
    id LONG
    tagNum LONG
    routing STRING
    lastActionCode STRING
    lastActionDesc STRING
    lastSeenStation STRING,
    lastSeenTimeGmt TIMESTAMP(4)
    bagArrivalDate TIMESTAMP(4)
    PRIMARY KEY(id)
  • ticket.bagInfo.flightLegs
    flightNo STRING
    flightDate TIMESTAMP(4)
    fltRouteSrc STRING
    fltRouteDest STRING
    estimatedArrival TIMESTAMP(4),
    actions JSON
    PRIMARY KEY(flightNo)
  • ticket.passengerInfo
    contactPhone STRING
    fullName STRING
    gender STRING
    PRIMARY KEY(contactPhone)

SQL 示例

示例 1:获取已签发机票的所有乘客的详细信息。
SELECT fullname, contactPhone, gender 
FROM NESTED TABLES 
(ticket a descendants(ticket.passengerInfo b))

解释:这是连接示例,其中目标表 ticket 与其子表 passengerInfo 联接。

输出:
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
{"fullname":"Adelaide Willard","contactPhone":"421-272-8082","gender":"M"}
{"fullname":"Dierdre Amador","contactPhone":"165-742-5715","gender":"M"}
{"fullname":"Doris Martin","contactPhone":"289-564-3497","gender":"F"}
{"fullname":"Adam Phillips","contactPhone":"893-324-1064","gender":"M"}
示例 1a:获取票证编号为 1762324912391 的乘客的详细信息。
SELECT fullname, contactPhone, gender 
FROM NESTED TABLES 
(ticket a descendants(ticket.passengerInfo b))
WHERE a.ticketNo=1762324912391

解释:这是连接示例,其中目标表 ticket 与其子表 passengerInfo 联接。此外,还可以通过对联接结果应用过滤条件来限制结果集。您将结果限制为特定票证编号。

输出:
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
示例 2:提取已签发机票的所有乘客的所有包详细信息。
SELECT * FROM NESTED TABLES 
(ticket a descendants(ticket.bagInfo b))

解释:这是连接示例,其中目标表 ticket 与其子表 bagInfo 联接。

输出:
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"tagNum":17657806255240,"routing":"MIA/LAX/MEL",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019-02-01T16:13:00.0000Z","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}}

{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}

{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
"b":{"ticketNo":1762392135540,"id":79039899156435,"tagNum":17657806224224,"routing":"GRU/ORD/SEA",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"SEA",
"lastSeenTimeGmt":"2019-02-15T21:21:00.0000Z","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}}

{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
"b":{"ticketNo":1762376407826,"id":7903989918469,"tagNum":17657806240229,"routing":"JFK/MAD",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MAD",
"lastSeenTimeGmt":"2019-03-07T13:51:00.0000Z","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}}

{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"tagNum":17657806232501,"routing":"BZN/SEA/CDG/MXP",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MXP",
"lastSeenTimeGmt":"2019-03-22T10:17:00.0000Z","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}}
示例 2a :提取特定票证编号的所有包详细信息。
SELECT * FROM 
NESTED TABLES (ticket a descendants(ticket.bagInfo b))
WHERE a.ticketNo=1762324912391

解释:这是连接示例,其中目标表 ticket 与其子表 bagInfo 联接。此外,还可以通过对联接结果应用过滤条件来限制结果集。您将结果限制为特定票证编号。

输出:
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}

注意:

如果将非联接谓词限制移动到 ON 子句,则结果集会包含满足 ON 子句条件的所有行。右外部表中不符合 ON 条件的行会填充 NULL 值,如下所示。
SELECT * FROM 
NESTED TABLES(ticket a descendants(ticket.bagInfo b 
ON a.ticketNo=b.ticketNo
AND a.ticketNo=1762324912391))
输出:
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":null}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":null}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"}, "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN", 
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},"b":null}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},"b":null}
示例 3:提取所有乘客的所有飞行行程详细信息。
SELECT * FROM 
NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))

解释:这是连接示例,其中目标表 ticket 与其后代 bagInfo 联接。子孙表可以是分层在表下的任何级别(例如,fightLegsbagInfo 的子项,它是 ticket 的子项,因此 flightLegsticket 的子项)。将提取 ticket 表中的所有行。如果 ticket 表中的任何行在 flightLegs 表中没有匹配行,则将为 flightLegs 表中的这些行显示 NULL 值。

输出:
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604","flightDate":"2019-02-01T06:00:00.0000Z",
"fltRouteSrc":"MIA","fltRouteDest":"LAX","estimatedArrival":"2019-02-01T11:00:00.0000Z",
"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}

{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667","flightDate":"2019-02-01T06:13:00.0000Z",
"fltRouteSrc":"LAX","fltRouteDest":"MEL","estimatedArrival":"2019-02-01T16:15:00.0000Z",
"actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}

{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM170","flightDate":"2019-03-15T08:13:00.0000Z",
"fltRouteSrc":"SLC","fltRouteDest":"BZN","estimatedArrival":"2019-03-15T10:14:00.0000Z",
"actions":[{"actionAt":"BZN","actionCode":"Offload to Carousel at BZN","actionTime":"2019-03-15T10:13:00Z"},
{"actionAt":"SLC","actionCode":"ONLOAD to BZN","actionTime":"2019-03-15T10:06:00Z"},
{"actionAt":"SLC","actionCode":"OFFLOAD from SLC","actionTime":"2019-03-15T09:59:00Z"}]}}

{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM490","flightDate":"2019-03-15T08:13:00.0000Z",
"fltRouteSrc":"CDG","fltRouteDest":"SLC","estimatedArrival":"2019-03-15T10:14:00.0000Z",
"actions":[{"actionAt":"CDG","actionCode":"ONLOAD to SLC","actionTime":"2019-03-15T09:42:00Z"},
{"actionAt":"CDG","actionCode":"BagTag Scan at CDG","actionTime":"2019-03-15T09:17:00Z"},
{"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-15T09:19:00Z"}]}}

{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM936","flightDate":"2019-03-15T08:00:00.0000Z",
"fltRouteSrc":"MXP","fltRouteDest":"CDG","estimatedArrival":"2019-03-15T09:00:00.0000Z",
"actions":[{"actionAt":"MXP","actionCode":"ONLOAD to CDG","actionTime":"2019-03-15T08:13:00Z"},
{"actionAt":"MXP","actionCode":"BagTag Scan at MXP","actionTime":"2019-03-15T07:48:00Z"},
{"actionAt":"MXP","actionCode":"Checkin at MXP","actionTime":"2019-03-15T07:38:00Z"}]}}

{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
"b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM79","flightDate":"2019-02-15T01:00:00.0000Z",
"fltRouteSrc":"GRU","fltRouteDest":"ORD","estimatedArrival":"2019-02-15T11:00:00.0000Z",
"actions":[{"actionAt":"GRU","actionCode":"ONLOAD to ORD","actionTime":"2019-02-15T01:21:00Z"},
{"actionAt":"GRU","actionCode":"BagTag Scan at GRU","actionTime":"2019-02-15T00:55:00Z"},
{"actionAt":"GRU","actionCode":"Checkin at GRU","actionTime":"2019-02-14T23:49:00Z"}]}}

{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"}
,"b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM907","flightDate":"2019-02-15T01:21:00.0000Z",
"fltRouteSrc":"ORD","fltRouteDest":"SEA","estimatedArrival":"2019-02-15T21:22:00.0000Z",
"actions":[{"actionAt":"SEA","actionCode":"Offload to Carousel at SEA","actionTime":"2019-02-15T21:16:00Z"},
{"actionAt":"ORD","actionCode":"ONLOAD to SEA","actionTime":"2019-02-15T20:52:00Z"},
{"actionAt":"ORD","actionCode":"OFFLOAD from ORD","actionTime":"2019-02-15T20:44:00Z"}]}}

{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
"b":{"ticketNo":1762376407826,"id":7903989918469,"flightNo":"BM495","flightDate":"2019-03-07T07:00:00.0000Z",
"fltRouteSrc":"JFK","fltRouteDest":"MAD","estimatedArrival":"2019-03-07T14:00:00.0000Z",
"actions":[{"actionAt":"MAD","actionCode":"Offload to Carousel at MAD","actionTime":"2019-03-07T13:54:00Z"},
{"actionAt":"JFK","actionCode":"ONLOAD to MAD","actionTime":"2019-03-07T07:00:00Z"},
{"actionAt":"JFK","actionCode":"BagTag Scan at JFK","actionTime":"2019-03-07T06:53:00Z"},
{"actionAt":"JFK","actionCode":"Checkin at JFK","actionTime":"2019-03-07T05:03:00Z"}]}}

{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM386","flightDate":"2019-03-22T07:23:00.0000Z",
"fltRouteSrc":"CDG","fltRouteDest":"MXP","estimatedArrival":"2019-03-22T10:24:00.0000Z",
"actions":[{"actionAt":"MXP","actionCode":"Offload to Carousel at MXP","actionTime":"2019-03-22T10:15:00Z"},
{"actionAt":"CDG","actionCode":"ONLOAD to MXP","actionTime":"2019-03-22T10:09:00Z"},
{"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-22T10:01:00Z"}]}}

{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM578","flightDate":"2019-03-22T07:23:00.0000Z",
"fltRouteSrc":"SEA","fltRouteDest":"CDG","estimatedArrival":"2019-03-21T23:24:00.0000Z",
"actions":[{"actionAt":"SEA","actionCode":"ONLOAD to CDG","actionTime":"2019-03-22T11:26:00Z"},
{"actionAt":"SEA","actionCode":"BagTag Scan at SEA","actionTime":"2019-03-22T10:57:00Z"},
{"actionAt":"SEA","actionCode":"OFFLOAD from SEA","actionTime":"2019-03-22T11:07:00Z"}]}}

{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM704","flightDate":"2019-03-22T07:00:00.0000Z",
"fltRouteSrc":"BZN","fltRouteDest":"SEA","estimatedArrival":"2019-03-22T09:00:00.0000Z",
"actions":[{"actionAt":"BZN","actionCode":"ONLOAD to SEA","actionTime":"2019-03-22T07:23:00Z"},
{"actionAt":"BZN","actionCode":"BagTag Scan at BZN","actionTime":"2019-03-22T06:58:00Z"},
{"actionAt":"BZN","actionCode":"Checkin at BZN","actionTime":"2019-03-22T05:20:00Z"}]}}
示例 3a:获取特定票证编号的所有飞行行程详细信息。
SELECT * FROM 
NESTED TABLES (ticket.bagInfo.flightLegs b ancestors(ticket a))
WHERE a.ticketNo=1762344493810

解释:这是连接示例,其中目标表 ticket 与其后代 bagInfo 联接。此外,还可以通过对联接结果应用过滤条件来限制结果集。您将结果限制为特定票证编号。

结果包含两行,表示此票证编号有两个飞行行程。

输出:
"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604",
"flightDate":"2019-02-01T06:00:00.0000Z","fltRouteSrc":"MIA","fltRouteDest":"LAX",
"estimatedArrival":"2019-02-01T11:00:00.0000Z",
"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}

{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667",
"flightDate":"2019-02-01T06:13:00.0000Z","fltRouteSrc":"LAX","fltRouteDest":"MEL",
"estimatedArrival":"2019-02-01T16:15:00.0000Z",
"actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}
示例 4:为所有乘客的所有行李提取行李 ID 和跃点数。
SELECT b.id,count(*) AS NUMBER_HOPS 
FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))
GROUP BY b.id

解释:您可以根据包 ID(使用 GROUP BY)对数据进行分组,并获取每个包的飞行行程计数(使用 count())。

输出:
{"id":79039899168383,"NUMBER_HOPS":3}
{"id":79039899156435,"NUMBER_HOPS":2}
{"id":7903989918469,"NUMBER_HOPS":1}
{"id":79039899165297,"NUMBER_HOPS":2}
{"id":79039899197492,"NUMBER_HOPS":3}
示例 4a:查找特定乘客的所有行李的跃点数。
SELECT b.id,count(*) AS NUMBER_HOPS FROM 
NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))
WHERE a.ticketNo=1762355527825 
GROUP BY b.id

解释:您可以根据包 ID(使用 GROUP BY)对数据进行分组,并获取每个包的飞行行程计数(使用 count())。此外,您还可以筛选特定票证编号的结果。

输出:
{"id":79039899197492,"NUMBER_HOPS":3}
示例 5:提取 2019 年之后到达的所有包的包 ID 和路由详细信息。
SELECT b.id, routing FROM 
NESTED TABLES(ticket a descendants(ticket.bagInfo b))
WHERE CAST (b.bagArrivalDate AS Timestamp(0))>= 
CAST ("2019-01-01T00:00:00" AS Timestamp(0))

解释:这是连接示例,其中目标表 ticket 与其子表 bagInfo 联接。过滤条件应用于 bagArrivalDateCAST 函数用于将字符串转换为时间戳,然后比较值。

输出:
{"id":79039899197492,"routing":"BZN/SEA/CDG/MXP"}
{"id":79039899165297,"routing":"MIA/LAX/MEL"}
{"id":79039899168383,"routing":"MXP/CDG/SLC/BZN"}
{"id":79039899156435,"routing":"GRU/ORD/SEA"}
{"id":7903989918469,"routing":"JFK/MAD"}

查询 API 示例

要执行查询,请使用 NoSQLHandle.query() API。

Download the full code TableJoins.java from the examples here.
   /* fetch rows based on joins*/
private static void fetchRows(NoSQLHandle handle,String sql_stmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sql_stmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)) {
         System.out.println("Query results:");
         for (MapValue res : results) {
            System.out.println("\t" + res);
         }
      }
}

System.out.println("Fetching data using NESTED TABLES:");
String sql_stmt_nt ="SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))";
/* fetching rows using nested tables*/
fetchRows(handle,sql_stmt_nt);

要执行查询,请使用 borneo.NoSQLHandle.query() 方法。

Download the full code TableJoins.py from the examples here.
# Fetch data from the table based on joins
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))

sql_stmt_nt='SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))'
print('Fetching data using NESTED TABLES ')
fetch_data(handle,sql_stmt_nt)

要执行查询,请使用 Client.Query 函数。

Download the full code TableJoins.go from the examples here.

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()))
   }
}

querystmt_nt:= "SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b)"
fmt.Println("Fetching data using NESTED TABLES")
fetchData(client, err,querystmt_nt)

要执行查询,请使用 query 方法。

JavaScript: Download the full code TableJoins.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);
   }
}

const stmt_nt = 'SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))';
console.log("Fetching data using NESTED TABLES");
await fetchData(handle,stmt_nt);
TypeScript: Download the full code TableJoins.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 stmt_nt = 'SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))';
console.log("Fetching data using NESTED TABLES");
await fetchData(handle,stmt_nt);

要执行查询,可以调用 QueryAsync 方法或调用 GetQueryAsyncEnumerable 方法并迭代生成的异步可枚举。

Download the full code TableJoins.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.Row
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}

private const string stmt_nt ="SELECT * FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))";
Console.WriteLine("Fetching data using NESTED TABLES: ");
await fetchData(client,stmt_nt);