The nesting of complex types within Tuple and user-defined types is permitted in CQL. The driver does not normalize such nested types, but rather the data is passed as a JSON-style string. For example, consider the table contacts which contains the columns id and contact. While id is a primitive int column, contact is a user-defined info column which contains name, email, and location fields. The location field itself is a nested user-defined address column which contains street, city, state, and zip fields. In CQL, the structure of this table would take the following form:

CREATE TYPE address (
   street varchar,
   city varchar,
   state varchar,
   zip int);
CREATE TYPE info (
   name varchar,
   email varchar,
   location frozen<address>);
CREATE TABLE contacts (
   id int PRIMARY KEY,
   contact frozen<info>);

The following tabular representation of the contacts table shows how the driver returns data when complex types are nested in other complex types. Because the complex user-defined type address is embedded in the complex user-defined type info, the entire CONTACT column is returned by the driver as a JSON string.

Note: You can retrieve this string data by calling the DatabaseMetaData.getColumns() method.
Table 1. CONTACTS (relational)
ID

(primary key)

 CONTACT
int info<name: varchar, email: varchar, location: address<street: varchar, city: varchar, state: varchar, zip: int>>
034 {name: 'Jude', email: 'jnichols@email.com', location: {street: '101 Main Street', city: 'Albany', state:'NY', zip: 12210}}
056 {name: 'Karen', email: 'kbrown@email.com', location: {street: '150 First Street', city: 'Portland', state: 'OR', zip: 97214}}

When executing SQL commands involving nested complex types, the data must be passed as a JSON string. Furthermore, the syntax you use to connote the JSON string depends on whether you are passing the string directly in a SQL command or binding the JSON string as a parameter to a variable in the application.

Note: Hints for parsing JSON-style strings are provided in the REMARK column of the getColumns() result.

Connoting the JSON-Style String in a SQL Statement

When passing the string directly in a SQL command, you must use the correct SQL syntax and escapes to maintain the structure of the data. To begin, the entire JSON string must be passed in single quotation marks ('). Furthermore, if the JSON string contains nested strings, two single quotation marks are used to indicate string values. The first quotation mark is an escape connoting the second embedded quotation mark. The following command inserts a new row into the CONTACTS table.

Note: In accordance with typical programming language syntax, the Insert statement is placed in double quotation marks. However, in the JSON string, two single quotation marks are used to indicate string values. The first quotation mark is an escape connoting the second embedded quotation mark.
SQLExecDirect(
   pstmt, 
   "INSERT INTO CONTACTS (ID, CONTACT) VALUES (075, '{name: ''Albert'', 
     email: ''aocampo@email.com'', location: {street: ''12 North Street'', 
     city: ''Durham'', state:''NC'', zip: 27704}}')",
   SQL_NTS);

After the insert has been executed, the Select command SELECT CONTACT FROM CONTACTS WHERE ID = 75 returns:

{name: 'Albert',
email: 'aocampo@email.com',
location: {street: '12 North Street',
           city: 'Durham',
           state:'NC',
           zip: 27704
           }
}

Connoting the JSON-Style String as a Parameter Variable

When binding the JSON string as a parameter to a variable in the application, you must follow your programming language syntax by placing the JSON string in double quotation marks. Escapes are not used to connote embedded single quotation marks. For example:

STRING string_variable =
   "{name: 'Albert', email: 'aocampo@email.com', 
      location: {street: '12 North Street', 
      city: 'Durham', state:'NC', zip: 27704}}"