Simple SELECT queries
If you want to follow along with the examples, download the script baggageschema_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
load
command, run the
script.load -file baggageschema_loaddata.sql
Fetch column data
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"}
Substituting column names in a query
You can use a different name for a column during a SELECT statement. Substituting a name in a query does not change the column name, but uses the substitute in the data returned.
CONTACTAT
in the
result.SELECT contactPhone AS CONTACTAT FROM BaggageInfo
Explanation: Here you want to fetch the contact phone of the passengers and display it as CONTACTAT.
{"CONTACTAT":"960-428-3843"}
{"CONTACTAT":"368-769-5636"}
{"CONTACTAT":"364-610-4444"}
{"CONTACTAT":"395-837-3772"}
{"CONTACTAT":"893-324-1064"}
{"CONTACTAT":"289-564-3497"}
{"CONTACTAT":"334-679-5105"}
{"CONTACTAT":"978-191-8550"}
{"CONTACTAT":"849-731-1334"}
{"CONTACTAT":"796-709-9501"}
{"CONTACTAT":"539-097-5220"}
{"CONTACTAT":"575-781-6240"}
{"CONTACTAT":"131-183-0560"}
{"CONTACTAT":"619-956-8760"}
{"CONTACTAT":"364-610-4444"}
{"CONTACTAT":"421-272-8082"}
{"CONTACTAT":"567-710-9972"}
{"CONTACTAT":"600-918-8404"}
{"CONTACTAT":"987-210-3029"}
{"CONTACTAT":"666-302-0028"}
{"CONTACTAT":"165-742-5715"}
You can combine columns using the concatenation operator "||" as shown below.
Example: For all customers, fetch the last place where the bag was seen and the time when it was seen.
SELECT "The bag was last seen at " ||
bag.bagInfo[].lastSeenStation || " on " ||
bag.bagInfo[].bagArrivalDate AS Bag_Details FROM BaggageInfo bag
{"Bag_Details":"The bag was last seen at BZN on 2019-03-15T10:13:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-04T10:08:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-25T20:15:00Z"}
{"Bag_Details":"The bag was last seen at MAD on 2019-03-07T13:51:00Z"}
{"Bag_Details":"The bag was last seen at FRA on 2019-03-02T13:18:00Z"}
{"Bag_Details":"The bag was last seen at VIE on 2019-02-12T07:04:00Z"}
{"Bag_Details":"The bag was last seen at JTRJTR on 2019-03-12T15:05:00Z2019-03-12T16:25:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-03-07T16:01:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-01T16:13:00Z"}
{"Bag_Details":"The bag was last seen at MXP on 2019-03-22T10:17:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-16T16:13:00Z"}
{"Bag_Details":"The bag was last seen at MIA on 2019-03-02T16:09:00Z"}
{"Bag_Details":"The bag was last seen at BZN on 2019-02-21T14:08:00Z"}
{"Bag_Details":"The bag was last seen at SGN on 2019-02-10T10:01:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-02-02T23:59:00Z"}
{"Bag_Details":"The bag was last seen at BLR on 2019-03-14T06:22:00Z"}
{"Bag_Details":"The bag was last seen at VIE on 2019-03-05T12:00:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-03-12T15:05:00Z"}
{"Bag_Details":"The bag was last seen at SEA on 2019-02-15T21:21:00Z"}
{"Bag_Details":"The bag was last seen at HKG on 2019-02-03T08:09:00Z"}
{"Bag_Details":"The bag was last seen at HKG on 2019-02-13T11:15:00Z"}
The result is cluttered if there is more than one bag per customer/reservation number as shown above.
bagInfo
array as shown
below.SELECT "The bag was last seen at " || [bag.bagInfo[].lastSeenStation] || " on " ||
[bag.bagInfo[].bagArrivalDate] AS Bag_Details FROM BaggageInfo bag
Note:
Column names and static text can also be concatenated using the "||" operator.Explanation: You are concatenating a part of the document in the
bagInfo
JSON with various static text and displaying it as elements
of an array.
{"Bag_Details":"The bag was last seen at [\"MIA\"] on [\"2019-03-02T16:09:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BZN\"] on [\"2019-02-21T14:08:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"SGN\"] on [\"2019-02-10T10:01:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"HKG\"] on [\"2019-02-13T11:15:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-02-02T23:59:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BLR\"] on [\"2019-03-14T06:22:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"VIE\"] on [\"2019-03-05T12:00:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-03-12T15:05:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"SEA\"] on [\"2019-02-15T21:21:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"HKG\"] on [\"2019-02-03T08:09:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BZN\"] on [\"2019-03-15T10:13:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-04T10:08:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-25T20:15:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MAD\"] on [\"2019-03-07T13:51:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"FRA\"] on [\"2019-03-02T13:18:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"VIE\"] on [\"2019-02-12T07:04:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\",\"JTR\"] on [\"2019-03-12T15:05:00Z\",
\"2019-03-12T16:25:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-03-07T16:01:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-01T16:13:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MXP\"] on [\"2019-03-22T10:17:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-16T16:13:00Z\"]"}
Filtering results in a query
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"]}