Sample data to run queries

Table 1: Airline baggage tracking application

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. One sample row is shown below.

The passenger's ticket number, ticketNo is the primary key of the table. The fullName, gender, contactPhone, and confNo (reservation number) fields store the passenger's information, which is part of a fixed schema. The bagInfo column is a schema-less JSON array, which represents the tracking information of a passenger's checked-in baggage.

Each element of the bagInfo array corresponds to a single checked-in bag. The size of the bagInfo array gives the total bags checked-in by a passenger. Each bag has an id and a tagnum field. The routing field includes the routing information from the passenger's travel itinerary. The lastActionCode and lastActionDesc fields hold the latest action taken on the bag and its action code at the current destination. The lastSeenStation field includes the airport code of the bag's current destination. The lastSeenTimeGmt field includes the latest action time. The bagArrivalDate field holds the expected arrival date at the destination airport. The bagInfo array further includes a nested flightLegs array with fields to track the source and transit details.

Each element of the flightLegs array corresponds to a travel leg. The fields flightNo holds the flight number, flightDate holds the departure date, fltRouteSrc holds the originating airport code, and fltRouteDest field hold the destination airport code for each travel leg. The flightLegs array further includes a nested actions array with fields to track the activities performed on the checked bag at each travel leg.

Each element of the actions array includes the fields actionAt, actionCode, and actionTime to track the tasks at source and destination airports in each travel leg.

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

Start your KVSTORE or KVLite and open the SQL shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

The baggageschema_loaddata.sql contains the following:

### Begin Script###
load -file baggageInfo.ddl
import -table baggageInfo -file baggageData.json
### End Script ###

Using the load command, run the script.

load -file baggageschema_loaddata.sql

Table 2: Streaming Media Service - Persistent user profile store

Download the script acctstream_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table. One sample row is shown below.

The subscriber's account ID, acct_id is the primary key of the table. The fields profile_name and account_expiry contain the subscriber’s details. The acct_data column is a schema-less JSON field, which keeps track of the subscriber's current activity.

Each element of the acct_data JSON represents a user with the given subscriber’s profile name. User data contains the fields firstName, lastName, and country to hold user information. The acct_data JSON field further includes a nested contentStreamed JSON array to track the shows watched by the user.

Each element of the contentStreamed array contains the showName field to store the name of the show. The showId field includes the identifier of the show. The showtype field indicates the type such as tvseries, sitcom, and so forth. The genres array lists the show’s categorization. The numSeasons field contains the total number of seasons streamed for the show. The contentStreamed JSON array also includes a nested seriesInfo JSON array to track the watched episodes.

Each element of the seriesInfo array contains a seasonNum field to identify the season. The numEpisodes field indicates the total number of episodes streamed in the given season. The seriesInfo array further includes an episodes array to track the details of each watched episode.

Each element of the episodes array contains the episodeID field to identify the episode. The episodeName field includes the episode's name. The lengthMin field includes the show’s telecast duration in minutes. The minWatched field includes the duration for which a user has watched the episode. The date field includes the date on which the user watched the given episode.

1,
123456789,
"AP",
"2023-10-18",
{
   "firstName": "Adam",
   "lastName": "Phillips",
   "country" : "Germany",
   "contentStreamed": [
      {
         "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"
                }
             ]
          }
       ]
    }
  ]
}

Start your KVSTORE or KVLite and open the SQL shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

The acctstream_loaddata.sql contains the following:

### Begin Script###
load -file acctstream.ddl
import -table stream_acct -file acctstreamData.json
### End Script ###

Using the load command, run the script.

load -file acctstream_loaddata.sql

Table 3: JSON collection table - Shopping application

The following code inserts data into the shopping application table.

The table used in shopping application is storeAcct. This table is a collection of documents with the shopper's contactPhone as the primary key. The rows represent individual shopper's records. The individual rows need not include the same fields in the document. The shopper's preferences such as name, address, email, notify, and so forth are stored as top-level fields in the document. The documents can include any number of JSON fields such as wishlist, cart, and orders that contain shopping-related information.

The JSON array wishlist contains the items wishlisted by the shoppers. Each element of this array includes nested JSON fields such as the item and priceperunit to store the product name and price details of the wishlisted item.

The JSON array cart contains the products that the shopper intends to purchase. Each element of this array includes nested JSON fields such as item, quantity, and priceperunit to store the product name, number of units, and price of each unit.

The JSON array orders contains the products that the shopper has purchased. Each element of this array includes nested JSON fields such as the orderID, item, priceperunit, EstDelivery, and status to store the order number, product name, price of each unit, estimated date of delivery for the product, and status of the order.

You can use this data to follow along with the examples explained in the topics.

insert into  storeAcct(contactPhone, firstName, lastName, address, cart) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, gender, address, notify, cart, wishlist) values("1917113999", "Sharon", "Willard", "F", {"street" : "Maine", "number" : 501, "city" : "San Jose", "state" : "San Francisco",   "zip" : 95095},"yes", [{"item" : "wallet", "quantity" : 2, "priceperunit" : 950},{"item" : "wall art", "quantity" : 1, "priceperunit" : 9500}], [{"item" : "Tshirt", "priceperunit" : 500},{"item" : "Jenga", "priceperunit" : 850}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1617114988", "Lorenzo", "Phil", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama",   "zip" : 95065},"yes", [{"item" : "A4 sheets", "quantity" : 2, "priceperunit" : 500},{"item" : "Mobile Holder", "quantity" : 1, "priceperunit" : 700}], [{"orderID" : "101200", "item" : "AG Novels 1", "EstDelivery" : "2023-11-15", "priceperunit" : 950, "status" : "Preparing to dispatch"},{"orderID" : "101200", "item" : "Wallpaper", "EstDelivery" : "2023-11-01", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, cart, orders) values("1517113582", "Dierdre", "Amador", {"street" : "Tex Ave", "number" : 651, "city" : "Houston", "state" : "TX",   "zip" : 95085}, NULL, [{"orderID" : "201200", "item" : "handbag", "EstDelivery" : "2023-11-01", "priceperunit" : 350},{"orderID" : "201201", "item" : "Lego", "EstDelivery" : "2023-11-01", "priceperunit" : 5500}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1417114488", "Doris", "Martin", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama",   "zip" : 95065},"yes", [{"item" : "Notebooks", "quantity" : 2, "priceperunit" : 50},{"item" : "Pens", "quantity" : 2, "priceperunit" : 50}], [{"orderID" : "301200", "item" : "Laptop Bag", "EstDelivery" : "2023-11-15", "priceperunit" : 1950, "status" : "Preparing to dispatch"},{"orderID" : "301200", "item" : "Mouse", "EstDelivery" : "2023-11-02", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;