Using JSON Collection Tables

A JSON collection table facilitates the creation of a schema-less table. You can define the primary key fields using several NoSQL data types (See Creating JSON collection table). You can add data into the table using an INSERT or UPSERT statement. Each row is a single document, which can contain any number of JSON fields.

You can create the JSON collection tables with optional TTL values or add the TTL values later by modifying the table. No other alteration is allowed on JSON collection tables.

Significance of JSON Collection Tables

JSON collection tables are useful for applications that store and retrieve data purely as documents. It is a convenient development paradigm for those applications where using a fixed schema is not optimal. Tables with a fixed schema require the specification of column names during creation and DML operations. The addition or removal of fields from fixed schema tables requires schema evolution, which is a costly operation.

As the JSON collection tables are schema-less, you do not have to modify the schema to add or remove documents from the table. The rows in the JSON collection tables can differ from one another in terms of the number of JSON fields that can be stored without predeclaring the JSON column names.

Creating JSON collection table

You can use the following syntax to create the JSON collection table.

Syntax

json_collection_definition ::= AS JSON COLLECTION

Semantics

You create a JSON collection table with one or more primary key fields. When you insert data into the table, a single document is created without an explicit declaration of a column of type JSON. The document can contain any number of JSON fields. The JSON fields must be valid JSON data types. Using non-JSON data types while inserting data will cause an exception. The primary key fields can include IDENTITY columns and UUIDs. The JSON collection tables can't be changed into fixed-schema tables.

You can create a JSON collection table with MR_COUNTERS if the table is intended to be a multi-region table. The MR_COUNTERS must be declared at the time of table creation and must be only top-level fields in the document. You can't include MR_COUNTERS elsewhere in the document.

You can supply an optional TTL value during the table creation. You can use the ALTER TABLE statement to only modify the TTL values.

The JSON collection tables support the following data types for the primary key fields:

Table 5-1 Data types for primary key fields in JSON collection tables

Field type	Supported data type
Primary key fields	integer long double float number string

The following examples demonstrate the creation of JSON collection tables. For inserting data into the tables, see Inserting Rows into JSON Collection Tables.

JSON collection table for a sample application

Example 5-11 Create a JSON collection table for a shopping application

CREATE TABLE storeAcct(contactPhone string, primary key(contactPhone)) AS JSON COLLECTION

Explanation: In the above DDL statement, you create a JSON collection table for a shopping application. This table includes the contactPhone as the primary key field. There is no need to supply any other field except the primary key field in the DDL command.

When you insert data into the table, the JSON collection table automatically considers the inserted fields other than the primary key fields to be JSON fields.

JSON collection table with more than one primary key fields

Example 5-12 Create a JSON collection table with an IDENTITY column as one of the primary key fields and a TTL value of 90 days

CREATE TABLE storeAcctComposite(contactPhone string, id integer generated by default as
      identity, primary key(contactPhone, id)) AS JSON COLLECTION USING TTL 90 DAYS

Explanation: The table includes two primary key fields, one of them is a contactPhone field and the other is id, which is declared as an IDENTITY column to autogenerate the order IDs. In this example, you create the table with a TTL value of 90 days. In the JSON collection DDL, you supply the USING TTL clause followed by the number of hours/days to create with TTL.

JSON collection table with MR_COUNTERS

Example 5-13 Create a JSON collection table for a shopping application with MR_COUNTERS in two regions

CREATE TABLE storeAcctMR(contactPhone string, primary key(contactPhone), mycounter as integer mr_counter) in regions FRA, LON AS JSON COLLECTION

Explanation: In the above DDL statement, you create a JSON collection table with an MR_COUNTER data type in two regions FRA and LON. To create regions, see CREATE REGION Statement.

You must define a table column as an MR_COUNTER along with its subtype during the table creation. When you insert data into this table, excluding primary key fields and MR_COUNTERS, all other supplied fields are implicitly added as JSON fields.

Note:

Declaring MR_COUNTERS in nested JSON fields in the document is not supported and will return an error if tried. The MR_COUNTERS must be top-level fields in the document.

Example 1: MR_COUNTER as a top-level field in the document after inserting data into the storeAcctMR table above.

{
  "contactPhone" : "1817113382",
  "address" : {
    "city" : "Houston",
    "number" : 401,
    "state" : "TX",
    "street" : "Tex Ave",
    "zip" : 95085
  },
  "firstName" : "Adam",
  "lastName" : "Smith",
  "mycounter" : 0
}

Example 2: In this example, you are trying to declare an MR_COUNTER (counter) in the address field of the document, which is not supported and returns the following error:

create table storeAcctMR(contactPhone  string, primary key(contactPhone), mycounter as integer mr_counter, address.counter as integer mr_counter) in regions FRA, LON as json collection


Error handling command create table storeAcctMR(contactPhone string, primary key(contactPhone), mycounter as integer mr_counter, address.counter as integer mr_counter) in regions FRA, LON as json collection: Error: Error found when creating the table: MR Counter in JSON Collection may contain only alphanumeric values plus the character "_": address.counter

Ensure that all the regions mentioned in the create table statement are predefined. Declaring regions that are not available during the table creation will result in an error.
Adding or removing the MR_COUNTERS from the JSON collection table through schema evolution is not supported.