Nested Complex Types

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.

Table 12. 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 setting the JSON string as a parameter on a prepared statement.

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 Java 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.

Stmt.executeUpdate(
"INSERT INTO contacts (id, contact) VALUES (075, '{name: ''Albert'', " +
"email: ''aocampo@email.com'', location: {street: ''12 North Street'', " +
"city: ''Durham'', state:''NC'', zip: 27704}}')");

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 Value on a Prepared Statement

When setting the JSON string as a parameter value, you must follow Java syntax by placing the JSON string in double quotation marks. Escapes are not used to connote embedded single quotation marks. For example:

pstmt.setString(
2,
"{name: 'Albert', email: 'aocampo@email.com', location: " +
"{street: '12 North Street', city: 'Durham', state:'NC', " +
"zip: 27704}}")