Chapter 7. The SQL for Oracle NoSQL Database Data Model

Table of Contents

Example Data
Data Types and Values
Wildcard Types and JSON Data
JSON Data
Timestamp
Timestamp Functions
Type Hierarchy
Subtype-Substitution Rule Exceptions
SQL for Oracle NoSQL Database Sequences
Sequence Concatenation Function

This chapter gives an overview of the data model for SQL for Oracle NoSQL Database. For a more detailed description of the data model see the SQL for Oracle NoSQL Database Specification.

Example Data

The language definition portion of this document frequently provides examples to illustrate the concepts. The following table definition is used by those examples:

CREATE TABLE Users (
  id INTEGER,
  firstName STRING,
  lastName STRING,
  otherNames ARRAY(RECORD(first STRING, last STRING)),
  age INTEGER,
  income INTEGER,
  address JSON,
  connections ARRAY(INTEGER),
  expenses MAP(INTEGER),
  moveDate timestamp(4),
  PRIMARY KEY (id)
) 

The rows of the Users table defined above represent information about users. For each such user, the “connections” field is an array containing ids of other users that this user is connected with. The ids in the array are sorted by some measure of the strength of the connection.

The “expenses” column is a maps expense categories (like “housing”, clothes”, “books”, etc) to the amount spent in the associated category. The set of categories may not be known in advance, or it may differ significantly from user to user, or may need to be frequently updated by adding or removing categories for each user. As a result, using a map type for “expenses”, instead of a record type, is the right choice.

Finally, the “address” column has type JSON. A typical value for “address” will be a map representing a json document.

Typical row data for this table will look like this:

{
  "id":1,
  "firstname":"David",
  "lastname":"Morrison",
  "otherNames" : [{"first" : "Dave",
                   "last" : "Morrison"}],
  "age":25,
  "income":100000,
  "address":{"street":"150 Route 2",
             "city":"Antioch",
             "state":"TN",
             "zipcode" : 37013,
             "phones":[{"type":"home", "areacode":423, 
                       "number":8634379}]
            },
  "connections":[2, 3],
  "expenses":{"food":1000, "gas":180},
  "moveDate" : "2016-10-29T18:43:59.8319"
}