Search in user defined type with Apache Cassandra

Question

In this example:

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

CREATE TABLE users (
    id uuid PRIMARY KEY,
    name text,
    addresses map<string, address>
)

How can I query users with city = newyork or find a user with a specific phone number.

Answer 1

This is not really a problem of querying a user-defined type: imagine that address would be a single text column and that addresses would contain a single address (ie. addresses TEXT); the problem would be the same.

Your user table is not meant to be query-able by anything else than the primary key, which in this case is the partition key, which is a UUID which makes it quasi useless.

If you want to query the users by name I would denormalize (that implies some duplication) and make a users_by_name table:

CREATE TABLE users_by_name(
  name TEXT,
  id UUID,
  addresses whatever,
  PRIMARY KEY((name), id)
)

where the users are stored by name (they should be unique) and the results will be retrieved sorted by id (id is the clustering key part of the primary key).

Same goes for query by addresses:

CREATE TABLE users_by_name(
  city TEXT,
  street TEXT,
  name TEXT,
  id UUID,
  PRIMARY KEY((city), street)
)

You might think that it does not really solve your problem, but it looks like you designed your data model from a relational DB (SQL) point of view, this is not the goal with Cassandra.