12 Working with JSON Collection Tables

Learn to create JSON collection tables, which are useful for applications that store and retrieve data purely as documents.

JSON Collection tables represent tables that are schema-less. That is, each row in a JSON collection table contains a JSON document, which itself contains a self-describing schema.

You create a JSON collection table as follows: 
CREATE TABLE PersonsJsonColl(id integer, primary key(id)) AS JSON COLLECTION USING TTL 90 DAYS;

JSON collection tables include one or more primary key fields. There is no need to explicitly declare other columns as type JSON. You can include an optional TTL value. If you want to include an MR_COUNTER data type, you must define a top-level attribute in your JSON document as an MR Counter along with its subtype during table creation.

You add data into the table using an INSERT or UPSERT statement. When you insert data, a single document is created, which can contain any number of JSON fields with valid JSON data types. You provide the value of primary key fields along with the other JSON fields in the document during the insertion of data.

You use one of the following methods to insert the data into a JSON collection table:
  • Using explicitly declared field names:
    INSERT into PersonsJsonColl(id, firstName, lastName, age, income, address) values("1", "David", "Morrison", 25, 100000, {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX", "zip" : 95085, "phones" : [{"type":"home", "areacode":423, "number":8634379}]})

    You must explicitly supply the primary key field followed by the top-level JSON field names in the INSERT statement. You include the corresponding values using the values clause.

  • Using positional values:
    INSERT into PersonsJsonColl values("2", {"firstName" : "David", "lastName" : "Morrison", "age" : 25, "income" : 100000, "address" : {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX", "zip" : 95085, "phones" : [{"type":"home", "areacode":423, "number":8634379}]}})

    You must supply the primary key field values followed by document fields as name/value pairs encapsulated in a single JSON object.

Note:

While inserting data into the JSON collection table with an MR_COUNTER data type, you must supply a value of 0 for the MR_COUNTER.
You can update data in the JSON collection tables using the UPDATE statement as follows:
UPDATE PersonsJsonColl p
SET p.age = 27,
ADD p.address.phones {"type":"office", "areacode":223, "number": 2634379},
PUT p.address {"Unit" : "D"},
REMOVE p.address.phones [$element.type = "home"]
WHERE p.id = 1 RETURNING *;

The statement above updates various fields of the PersonsJsonColl table by using the SET, ADD, PUT, and REMOVE clauses.

You get the following output:
{"id":1,"address":{"Unit":"D","city":"Houston","number":401,"phones":[{"areacode":223,"number":2634379,"type":"office"}],"state":"TX","street":"Tex Ave","zip":95085},"age":27,"firstName":"David","income":100000,"lastName":"Morrison"}
You can index the fields in a JSON collection table by specifying the name of the indexed element and ANYATOMIC for the type definition. For strongly typed indexes, you can specify the JSON type of the fields being indexed.
create index myindex on PersonsJsonColl(age as ANYATOMIC);
The statement above creates an untyped index on the age field. If the element you want to index is deeply nested in a JSON object, you must specify the complete path expression to the field as follows:
create index idx_income_cty on storeAcct (income as ANYATOMIC, address.city as ANYATOMIC);

The statement above creates a composite index using top-level income field and a nested city field.