使用 NESTED TABLES 與父項 - 子項表格

JOIN 是用來結合兩個或多個表格的資料列 (根據它們之間的相關資料欄)。在階層式表格中，子項表格會繼承其父項表格的主索引鍵資料欄。這會以隱含方式執行，而不會在子項的 CREATE TABLE 敘述句中包含父項資料欄。階層中的所有表格都有相同的分區索引鍵資料欄。

您可以使用 NESTED TABLES 子句來結合 Oracle NoSQL Database 中的表格。

• NESTED 表格概要

• 使用 NESTED TABLES 的範例

NESTED 表格概要

NESTED TABLES 子句會指定參與表格，並將它們分成 3 個群組。首先，指定擷取資料的來源目標表格。接著，ANCESTORS 子句 (如果有的話) 會指定表格階層中必須為目標表格之祖系的表格數目。最後，如果有的話，DESCENDANTS 子句會指定表格階層中必須為目標表格之子系的表格數目。

注意：通常，NESTED TABLES 子句等同於目標表格周圍「置中」的一些左外部結合作業。

NESTED 表格的特性：

• 在相同階層中查詢多個表格

• 它不是 ANSI-SQL 標準

• 它支援同層級表格結合

表格 - 巢狀表格 Vs 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 輸入模式，瞭解使用 DDL 敘述句建立表格的步驟。

若要將資料載入從 OCI 主控台建立的表格，請按一下表格名稱。就會顯示表格的詳細資訊。按一下上傳資料。按一下選取要上傳的檔案並提供要上傳的 JSON 檔案。您可以從此處下載父項子項表格的 DDL 和 JSON 檔案。

使用 NESTED TABLES 的範例

範例中使用的各種表格：

• ticket - 許可

  ticketNo LONG
  confNo STRING
  PRIMARY KEY(ticketNo)

• 中文 (繁體)

  id LONG
  tagNum LONG
  routing STRING
  lastActionCode STRING
  lastActionDesc STRING
  lastSeenStation STRING,
  lastSeenTimeGmt TIMESTAMP(4)
  bagArrivalDate TIMESTAMP(4)
  PRIMARY KEY(id)

• 機票。包資訊。航班航段

  flightNo STRING
  flightDate TIMESTAMP(4)
  fltRouteSrc STRING
  fltRouteDest STRING
  estimatedArrival TIMESTAMP(4),
  actions JSON
  PRIMARY KEY(flightNo)

• 機票。乘客資訊

  contactPhone STRING
  fullName STRING
  gender STRING
  PRIMARY KEY(contactPhone)

• SQL 範例

• 查詢 API 範例

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 結合。子代表格可以是表格下方任何階層的層次 (例如 fightLegs 是 bagInfo 的子項，其為 ticket 的子項，因此 flightLegs 是 ticket 的子代)。將會擷取 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

說明：您可以根據包裝袋識別碼 (使用 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

說明：您可以根據袋識別碼 (使用 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 結合的結合範例。篩選條件會套用至 bagArrivalDate。CAST 函數是用來將字串轉換成「時戳」，然後比較值。

輸出：

{"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 範例

• Java
• Python
• 移至
• Node.js
• C#

若要執行查詢，請使用 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() 方法。

從此處的範例下載完整代碼 TableJoins.py 。

# 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 函數。

從此處的範例下載完整代碼 TableJoins.go 。

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：請從此處的範例下載完整程式碼 TableJoins.js 。

//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：從此處的範例下載完整的程式碼 TableJoins.ts 。

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);

相關主題

• 建立表格
• 新增資料