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)
)

Table 3: JSON collection table - Shopping application

JSON collection tables are useful for applications that store and retrieve data purely as documents. JSON collection tables are schema-less tables, which provide the flexibility to create tables with primary key field declaration. You must supply the value of primary key fields along with the other fields in the document during the insertion of data into the 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.

The following code creates the table:

CREATE TABLE IF NOT EXISTS storeAcct (
contactPhone STRING, 
PRIMARY KEY(SHARD(contactPhone))
) AS JSON COLLECTION