Previous Next JavaScript must be enabled to correctly display this content

## 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 *;