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.

Example 1: Choose all data from the table 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.

Output (displaying only a row of the result for brevity):

{"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"}
]}

Example 2: To choose specific column(s) from the table 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.

Output:

{"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"}

Example 3: Choose all data from the table stream_acct.

SELECT * FROM stream_acct

Explanation: The stream_acct schema has some fixed static fields and a JSON column.

Output (displaying only a row of the result for brevity):

{"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 <= .

Example 1: Find the tag number of a passenger's baggage along with the passenger's full name for a given reservation number FH7G1W.

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.

Output:

{"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"
  } ]

Example 2: Where was the baggage with a given reservation number FH7G1W last seen? Also, fetch the tag number of the baggage.

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.

Output:

{"fullName":"Rosalia Triplett","tagNum":"17657806215913",
"lastSeenStation":"VIE"}

Example 3: Select details of the bags( tag and last seen time) for a passenger with ticket number 1762340579411.

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.

Output:

{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"lastSeenStation":["JTR","JTR"]}

Example 4: Fetch the last name, account expiry date and the shows watched by the user with 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.

Output:

{"account_expiry":"2023-10-18T00:00:00.0Z","lastName":"Phillips","showName":["At the Ranch","Bienvenu"]}