使用主控台擷取資料
使用 SQL 命令擷取資料
您可以使用 SQL SELECT 敘述句從您的 NoSQL 表格擷取資料。
從表格擷取所有資料列
您可以從表格中選擇資料欄。若要這樣做，請在敘述句中列出 SELECT 之後所需表格資料欄的名稱。您可以在 FROM 子句之後指定表格的名稱。若要從子項表格擷取資料，請使用點標記法，例如 parent.child 。若要選擇所有表格資料欄，請使用星號 (*) 萬用字元。SELECT 敘述句也可以包含以現有資料欄值為基礎的運算表示式。
範例 1：從表格
BaggageInfo 選擇所有資料。SELECT * FROM BaggageInfo說明：BaggageInfo 綱要包含固定的靜態欄位和 JSON 資料欄。靜態欄位包括機票號碼、全名、性別、聯絡電話和確認號碼。包裝資訊會儲存為 JSON，並填入各種文件。
輸出 (只顯示一列漏洞的結果)：
{"ticketNo":1762330498104,"fullName":"Michelle Payne","gender":"F","contactPhone":"575-781-6240","confNo":"RL3J4Q",
"bagInfo":[{
"bagArrivalDate":"2019-02-02T23:59:00Z",
"flightLegs":[
{"actions":[
{"actionAt":"SFO","actionCode":"ONLOAD to IST","actionTime":"2019-02-02T12:10:00Z"},
{"actionAt":"SFO","actionCode":"BagTag Scan at SFO","actionTime":"2019-02-02T11:47:00Z"},
{"actionAt":"SFO","actionCode":"Checkin at SFO","actionTime":"2019-02-02T10:01:00Z"}],
"estimatedArrival":"2019-02-03T01:00:00Z",
"flightDate":"2019-02-02T12:00:00Z",
"flightNo":"BM318",
"fltRouteDest":"IST",
"fltRouteSrc":"SFO"},
{"actions":[
{"actionAt":"IST","actionCode":"ONLOAD to ATH","actionTime":"2019-02-03T13:06:00Z"},
{"actionAt":"IST","actionCode":"BagTag Scan at IST","actionTime":"2019-02-03T12:48:00Z"},
{"actionAt":"IST","actionCode":"OFFLOAD from IST","actionTime":"2019-02-03T13:00:00Z"}],
"estimatedArrival":"2019-02-03T12:12:00Z",
"flightDate":"2019-02-02T13:10:00Z",
"flightNo":"BM696",
"fltRouteDest":"ATH",
"fltRouteSrc":"IST"},
{"actions":[
{"actionAt":"JTR","actionCode":"Offload to Carousel at JTR","actionTime":"2019-02-03T00:06:00Z"},
{"actionAt":"ATH","actionCode":"ONLOAD to JTR","actionTime":"2019-02-03T00:13:00Z"},
{"actionAt":"ATH","actionCode":"OFFLOAD from ATH","actionTime":"2019-02-03T00:10:00Z"}],
"estimatedArrival":"2019-02-03T00:12:00Z",
"flightDate":"2019-2-2T12:10:00Z",
"flightNo":"BM665",
"fltRouteDest":"JTR",
"fltRouteSrc":"ATH"}],
"id":"79039899186259",
"lastActionCode":"OFFLOAD",
"lastActionDesc":"OFFLOAD",
"lastSeenStation":"JTR",
"lastSeenTimeGmt":"2019-02-02T23:59:00Z",
"routing":"SFO/IST/ATH/JTR",
"tagNum":"17657806247861"}
]}範例 2：若要從表格
BaggageInfo 選擇特定的資料欄，請在 SELECT 敘述句中以逗號分隔的清單包括資料欄名稱。SELECT fullName, contactPhone, gender FROM BaggageInfo說明：您要顯示三個靜態欄位的值 - 全名、電話號碼及性別。
輸出：
{"fullName":"Lucinda Beckman","contactPhone":"364-610-4444","gender":"M"}
{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","gender":"M"}
{"fullName":"Raymond Griffin","contactPhone":"567-710-9972","gender":"F"}
{"fullName":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
{"fullName":"Zina Christenson","contactPhone":"987-210-3029","gender":"M"}
{"fullName":"Zulema Martindale","contactPhone":"666-302-0028","gender":"F"}
{"fullName":"Dierdre Amador","contactPhone":"165-742-5715","gender":"M"}
{"fullName":"Henry Jenkins","contactPhone":"960-428-3843","gender":"F"}
{"fullName":"Rosalia Triplett","contactPhone":"368-769-5636","gender":"F"}
{"fullName":"Lorenzo Phil","contactPhone":"364-610-4444","gender":"M"}
{"fullName":"Gerard Greene","contactPhone":"395-837-3772","gender":"M"}
{"fullName":"Adam Phillips","contactPhone":"893-324-1064","gender":"M"}
{"fullName":"Doris Martin","contactPhone":"289-564-3497","gender":"F"}
{"fullName":"Joanne Diaz","contactPhone":"334-679-5105","gender":"F"}
{"fullName":"Omar Harvey","contactPhone":"978-191-8550","gender":"F"}
{"fullName":"Fallon Clements","contactPhone":"849-731-1334","gender":"M"}
{"fullName":"Lisbeth Wampler","contactPhone":"796-709-9501","gender":"M"}
{"fullName":"Teena Colley","contactPhone":"539-097-5220","gender":"M"}
{"fullName":"Michelle Payne","contactPhone":"575-781-6240","gender":"F"}
{"fullName":"Mary Watson","contactPhone":"131-183-0560","gender":"F"}
{"fullName":"Kendal Biddle","contactPhone":"619-956-8760","gender":"F"}範例 3：從表格
stream_acct 選擇所有資料。SELECT * FROM stream_acct說明：stream_acct 綱要包含固定的靜態欄位和 JSON 資料欄。
輸出 (只顯示一列漏洞的結果)：
{"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.0Z",
"acct_data":{
[{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": ["action", "crime", "spanish"],
"numSeasons": 4,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 85,
"minWatched": 85,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 60,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}]
}]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": ["comedy", "french"],
"numSeasons": 2,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}]
}]
}]}}從表格篩選資料
您可以在 WHERE 子句中指定篩選條件來篩選查詢結果。篩選條件通常是由一或多個透過邏輯運算子 AND 或 OR 連接的比較表示式所組成。也支援以下比較運算子：=、！=、>、>=、< 及 <=。
範例 1：尋找乘客行李的標籤編號，以及指定預訂編號 FH7G1W 的乘客全名。
SELECT bag.fullName, bag.bagInfo[].tagNum FROM BaggageInfo bag
WHERE bag.confNo="FH7G1W"說明：擷取與指定預留編號對應的標記編號。
輸出：
{"fullName":"Rosalia Triplett","tagNum":"17657806215913"}附註：
為了更加瞭解，下面顯示具有所有靜態欄位與 bagInfo JSON 的資料列。"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
[ {
"id" : "79039899165297",
"tagNum" : "17657806255240",
"routing" : "MIA/LAX/MEL",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"flightLegs" : [ {
"flightNo" : "BM604",
"flightDate" : "2019-02-01T01:00:00",
"fltRouteSrc" : "MIA",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-02-01T03:00:00",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T01:13:00"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T00:47:00"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T23:38:00"
} ]
}, {
"flightNo" : "BM667",
"flightDate" : "2019-01-31T22:13:00",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MEL",
"estimatedArrival" : "2019-02-02T03:15:00",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-02T03:15:00"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T07:35:00"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T07:18:00"
} ]
} ],
"lastSeenTimeGmt" : "2019-02-02T03:13:00",
"bagArrivalDate" : "2019.02.02T03:13:00"
} ]範例 2：上次看到指定保留編號為 FH7G1W 的行李在哪裡？此外，提取行李的標籤編號。
SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"說明：bagInfo 是 JSON，並填入文件陣列。可針對特定預訂編號擷取全名與最後查看的車站。
輸出：
{"fullName":"Rosalia Triplett","tagNum":"17657806215913",
"lastSeenStation":"VIE"}範例 3：為機票號碼為 1762340579411 的乘客選取行李的明細 (標記與上次看到的時間)。
SELECT bag.ticketNo, bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag where bag.ticketNo=1762320369957說明：bagInfo 是 JSON，並填入文件陣列。可以為特定票證號碼擷取全名、標籤號碼和最後一次看到的車站。
輸出：
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"lastSeenStation":["JTR","JTR"]}範例 4：擷取使用者名稱、帳戶到期日，以及使用者觀看的
acct_id 1 顯示。SELECT account_expiry, acct.acct_data.lastName, acct.acct_data.contentStreamed[].showName FROM stream_acct acct WHERE acct_id=1說明：acct_data 是 JSON，並填入文件陣列。系統會擷取特定帳戶 ID 的姓氏、帳戶到期日及顯示名稱。
輸出：
{"account_expiry":"2023-10-18T00:00:00.0Z","lastName":"Phillips","showName":["At the Ranch","Bienvenu"]}