Using a user-defined type

An example of creating a user-defined type to attach multiple data fields to a column.

In Apache Cassandra™ 2.1 and later, you can create a user-defined type to attach multiple data fields to a column. This example shows how to accomplish these tasks:

Create the user-defined types address and fullname.
Create a table that defines map and set collection columns of the address and fullname types, respectively.
Include a name column, also of the fullname type, in the table.
Insert the street, city, and postal code in the addresses column.
Insert the first and last name of a person in the name column.
Filter data on a column of a user-defined type.
Insert a names of direct reports in the set collection.

Procedure

Create a keyspace.

CREATE KEYSPACE mykeyspace WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'datacenter1' : 1 };

Create a user-defined type named address.

CREATE TYPE mykeyspace.address (
  street text,
  city text,
  zip_code int,
  phones set<text>
);

Create a user-defined type for the name of a user.

CREATE TYPE mykeyspace.fullname (
  firstname text,
  lastname text
);

Create a table for storing user data in columns of type fullname and address. Use the frozen keyword in the definition of the user-defined type column.

CREATE TABLE mykeyspace.users (
  id uuid PRIMARY KEY,
  name frozen <fullname>,
  direct_reports set<frozen <fullname>>,     // a collection set
  addresses map<text, frozen <address>>     // a collection map
);

Insert a user's name into the fullname column.

INSERT INTO mykeyspace.users (id, name) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, {firstname: 'Marie-Claude', lastname: 'Josset'});

Insert an address labeled home into the table.

UPDATE mykeyspace.users SET addresses = addresses + {'home': { street: '191 Rue St. Charles', city: 'Paris', zip_code: 75015, phones: {'33 6 78 90 12 34'}}} WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;

Retrieve the full name of a user.
```
SELECT name FROM mykeyspace.users WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;
```
Using the column name of a user-defined type retrieves all fields: firstname and lastname.
```
name
-------------------------------------------------
 {firstname: 'Marie-Claude', lastname: 'Josset'}
```
Using dot notation, you can retrieve a component of the user-defined type column, for example just the last name.
```
SELECT name.lastname FROM mykeyspace.users WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;

 name.lastname
---------------
        Josset
```
Filter data on a column of a user-defined type. Create an index and then run a conditional query. In Cassandra 2.1.x, you need to list all components of the name column in the WHERE clause.
```
CREATE INDEX on mykeyspace.users (name);

SELECT id FROM mykeyspace.users WHERE name = {firstname: 'Marie-Claude', lastname: 'Josset'};
```
Output is:
```
 id
--------------------------------------
 62c36092-82a1-3a00-93d1-46196ee77204
```
Insert names of people who report to Marie-Claude. Use the UPDATE command. Insert the name of a person who reports to another manager using the INSERT command.
When using the frozen keyword, you cannot update parts of a user-defined type value. The entire value must be overwritten. Cassandra treats the value of a frozen, user-defined type like a blob.
```
UPDATE mykeyspace.users SET direct_reports = { ( 'Naoko', 'Murai'), ( 'Sompom', 'Peh') } WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;

INSERT INTO mykeyspace.users (id, direct_reports) VALUES ( 7db1a490-5878-11e2-bcfd-0800200c9a66, { ('Jeiranan', 'Thongnopneua') } );
```

Query the table for the direct reports to Marie-Claude.

SELECT direct_reports FROM mykeyspace.users;

 direct_reports
-----------------------------------------------------------------------------------
                            {{firstname: 'Jeiranan', lastname: 'Thongnopneua'}}
 {{firstname: 'Naoko', lastname: 'Murai'}, {firstname: 'Sompom', lastname: 'Peh'}}