Using Console to fetch data
Learn how to view and download Oracle NoSQL Database Cloud Service table data using Console.
This article has the following topics:
Related Topics
Viewing Table Data
Learn how to view data in Oracle NoSQL Database Cloud Service tables from the NoSQL console.
Downloading Table Data
Learn how to download data in Oracle NoSQL Database Cloud Service tables from the NoSQL console.
Using SQL commands to fetch data
You can use SQL SELECT statement to fetch data from your NoSQL table.
Fetching all rows from a table
You can choose columns from a table. To do so, list the names of the desired table columns after SELECT in the statement. You give the name of the table after the FROM clause. To retrieve data from a child table, use dot notation, such as parent.child. To choose all table columns, use the asterisk (*) wildcard character. The SELECT statement can also contain computational expressions based on the values of existing columns.
BaggageInfo
.SELECT * FROM BaggageInfo
Explanation: The BaggageInfo
schema has some
fixed static fields and a JSON column. The static fields are ticket number, full
name, gender, contact phone, and confirmation number. The bag information is stored
as JSON and is populated with an array of documents.
{"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"}
]}
BaggageInfo
, include the column names as a comma-separated list
in the SELECT
statement.SELECT fullName, contactPhone, gender FROM BaggageInfo
Explanation: You want to display the values of three static fields - full name, phone number, and gender.
{"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"}
stream_acct
.SELECT * FROM stream_acct
Explanation: The stream_acct
schema has some
fixed static fields and a JSON column.
{"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"
}]
}]
}]}}
Filter data from a table
You can filter query results by specifying a filter condition in the WHERE clause. Typically, a filter condition consists of one or more comparison expressions connected through logical operators AND or OR. The following comparison operators are also supported: =, !=, >, >=, <, and <= .
SELECT bag.fullName, bag.bagInfo[].tagNum FROM BaggageInfo bag
WHERE bag.confNo="FH7G1W"
Explanation: You fetch the tag number corresponding to a given reservation number.
{"fullName":"Rosalia Triplett","tagNum":"17657806215913"}
Note:
For better understanding, the row of data with all the static fields and the bagInfo JSON is shown below."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"
} ]
SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"
Explanation: The bagInfo
is JSON and is populated
with an array of documents. The full name and the last seen station can be fetched
for a particular reservation number.
{"fullName":"Rosalia Triplett","tagNum":"17657806215913",
"lastSeenStation":"VIE"}
SELECT bag.ticketNo, bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag where bag.ticketNo=1762320369957
Explanation: The bagInfo
is JSON and is
populated with an array of documents. The full name, tag number, and last seen
station can be fetched for a particular ticket number.
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"lastSeenStation":["JTR","JTR"]}
acct_id
1.SELECT account_expiry, acct.acct_data.lastName, acct.acct_data.contentStreamed[].showName FROM stream_acct acct WHERE acct_id=1
Explanation: The acct_data
is JSON and is
populated with an array of documents. The last name, account expiry date and show
names are fetched for a particular account id.
{"account_expiry":"2023-10-18T00:00:00.0Z","lastName":"Phillips","showName":["At the Ranch","Bienvenu"]}