Sample use-cases for NDCS

An airline baggage tracking application and a streaming media service to persistently store user profile are two real-time uses cases of NoSQL Database Cloud Service.

The SQL for Oracle NoSQL Database language provides a SQL-like interface to Oracle NoSQL Database. The SQL for Oracle NoSQL Database data model supports flat relational data, hierarchical typed (schema-full) data, and schema-less JSON data. You have the flexibility to create tables with a well-defined schema for applications that require fixed data or a combination of fixed data and schema-less JSON. Oracle NoSQL Database supports many of the most popular programming languages and frameworks with idiomatic language APIs and data structures, giving your application language native access to data stored in NoSQL Database. It currently supports the following programming languages and frameworks: Javascript (Node.js), Python, Java, Golang, C#/.NET, and Spring Data. You can also navigate the database as you develop your code with plugins for one of the following supported integrated development environments: Visual Studio Code,IntelliJ, or Eclipse.

This article has the following topics:

Sample use-cases used in the examples

You have two different schemas (with real-time scenarios) for learning various SQL concepts. These two schemas will include various data types that can be used in the Oracle NoSQL database.

Schema 1: BaggageInfo schema

Using this schema you can handle a use case wherein passengers traveling on a flight can track the progress of their checked-in bags or luggage along the route to the final destination. This functionality can be made available as part of the airline's mobile application. Once the passenger logs into the mobile application, the ticket number or reservation code of the current flight is displayed on the screen. Passengers can use this information to search for their baggage information. The mobile application is using NoSQL Database to store all the data related to the baggage. In the backend, the mobile application logic performs SQL queries to retrieve the required data.

Schema 2: Streaming Media Service - Persistent User Profile Store

Consider a TV streaming application. It streams various shows that are watched by customers across the globe. Every show has a number of seasons and every season has multiple episodes. You need a persistent meta-data store that keeps track of the current activity of the customers using the TV streaming application. Using this schema you can provide useful information to the customer such as episodes they watched, the watch time per episode, the total number of seasons of the show they watched, etc. The data is stored in the NoSQL Database and the application performs SQL queries to retrieve the required data and make it available to the user.

Tables used in the examples

The table is the basic structure to hold user data.

Table 1: Airline baggage tracking application

The table used in this schema is BaggageInfo. This schema has a combination of fixed data types like LONG, STRING. It also has a schema-less JSON (bagInfo) as one of its columns. The schema-less JSON does not have a fixed data type. The bag information of the passengers is a schema-less JSON. In contrast, the passenger's information like ticket number, full name, gender, contact details is all part of a fixed schema. You can add any number of fields to this non-fixed schemaless JSON field. .

The following code creates the table.
CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

Table 2: Streaming Media Service - Persistent user profile store

The table used in this schema is stream_acct. The primary key in this schema is acct_id. The schema also includes a JSON column (acct_data), which is schema-less. The schema-less JSON does not have a fixed data type. You can add any number of fields to this non-fixed schema-less JSON field.

The following code creates the table.
CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)

Describe tables

You use DESCRIBE or DESC command to view the description of a table.
(DESCRIBE | DESC) [AS JSON] TABLE table_name [ "(" field_name")"]

AS JSON can be specified if you want the output to be in JSON format. You could get information about a specific field in any table by providing the field name.

Example 1: Describe a table
DESCRIBE TABLE stream_acct
Output:
 === Information ===
 +-------------+-----+-------+----------------+----------+--------+----------+---------+---------+-------------+
 |    name     | ttl | owner | jsonCollection | sysTable | parent | children | regions | indexes | description |
 +-------------+-----+-------+----------------+----------+--------+----------+---------+---------+-------------+
 | stream_acct |     |       | N              | N        |        |          |         |         |             |
 +-------------+-----+-------+----------------+----------+--------+----------+---------+---------+-------------+

 === Fields ===
 +----+----------------+--------------+----------+---------+----------+------------+----------+
 | id |      name      |     type     | nullable | default | shardKey | primaryKey | identity |
 +----+----------------+--------------+----------+---------+----------+------------+----------+
 |  1 | acct_id        | Integer      | N        | NULL    | Y        | Y          |          |
 +----+----------------+--------------+----------+---------+----------+------------+----------+
 |  2 | profile_name   | String       | Y        | NULL    |          |            |          |
 +----+----------------+--------------+----------+---------+----------+------------+----------+
 |  3 | account_expiry | Timestamp(9) | Y        | NULL    |          |            |          |
 +----+----------------+--------------+----------+---------+----------+------------+----------+
 |  4 | acct_data      | Json         | Y        | NULL    |          |            |          |
 +----+----------------+--------------+----------+---------+----------+------------+----------+
                                                                                                                                                           ---+----------+
Example 2: Describe a table and display the output as JSON
DESC AS JSON TABLE BaggageInfo
Output: 
{
  "json_version" : 1,
  "type" : "table",
  "name" : "BaggageInfo",
  "fields" : [{
    "name" : "ticketNo",
    "type" : "LONG",
    "nullable" : false
  }, {
    "name" : "fullName",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "gender",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "contactPhone",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "confNo",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "bagInfo",
    "type" : "JSON",
    "nullable" : true
  }],
  "primaryKey" : ["ticketNo"],
  "shardKey" : ["ticketNo"]
}
Example 3: Describe one particular field of a table
DESCRIBE TABLE BaggageInfo (ticketNo)
Output:
 +----+----------+------+----------+---------+----------+------------+----------+
 | id |   name   | type | nullable | default | shardKey | primaryKey | identity |
 +----+----------+------+----------+---------+----------+------------+----------+
 |  1 | ticketNo | Long | N        | NULL    | Y        | Y          |          |
 +----+----------+------+----------+---------+----------+------------+----------+

Sample data to run queries

You can create tables used in the examples and load data into the tables using OCI console.

Table 1: Airline baggage tracking application: One sample row of the baggage application 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"
  } ]
From the OCI console, use the Advanced DDL Input Mode to create the table using the DDL given below.
CREATE TABLE IF NOT EXISTS BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

See Creating Singleton Table: Advanced DDL Input Mode for steps to create a table with a DDL statement.

To load data into the table created from the OCI console, click on the table name. The details of the table is displayed. Click Upload Data. Click select file to upload and provide the JSON file to be uploaded. You can download the DDL and JSON file for the airline tracking application here.

Table 2: Streaming Media Service - Persistent user profile store One sample row of the streaming media application is shown below. 
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"
                }
             ]
          }
       ]
    }
  ]
}
From the OCI console, use the Advanced DDL Input Mode to create the table using the DDL given below.
CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)

See Creating Singleton Table: Advanced DDL Input Mode for steps to create a table with a DDL statement.

To load data into the table created from the OCI console, click on the table name. The details of the table is displayed. Click Upload Data. Click select file to upload and provide the JSON file to be uploaded. You can download the DDL and JSON file for the airline tracking application here.