YCQL中判断集合是否含值及SET类型的查询与索引问题

Great question! Let's break this down into two clear parts to address both your concerns.

1. Checking if SET/MAP/LIST contains a value #

YCQL has native support for checking membership in collection types using the CONTAINS operator (and its variants for maps). Let's go through each case with examples:

For SET types #

Your sample query is actually valid! If you've created the table like this:

CREATE TABLE test.table_name(
  id text,
  ckk SET<INT>,
  PRIMARY KEY((id))
);

You can absolutely use CONTAINS to filter rows where the ckk set includes the value 4:

SELECT * FROM test.table_name WHERE id = 1 AND ckk CONTAINS 4;

This will return all rows where the partition key id is 1 and the ckk set contains the integer 4.

For MAP types #

Maps have two variants to check membership:

• Use CONTAINS KEY to verify if the map has a specific key
• Use CONTAINS VALUE to check if the map holds a specific value

Example table and queries:

CREATE TABLE test.map_table(
  id text,
  user_data MAP<TEXT, INT>,
  PRIMARY KEY((id))
);

-- Check if user_data includes the key "age"
SELECT * FROM test.map_table WHERE id = 'user1' AND user_data CONTAINS KEY 'age';

-- Check if user_data has the value 30
SELECT * FROM test.map_table WHERE id = 'user1' AND user_data CONTAINS VALUE 30;

For LIST types #

For lists, the CONTAINS operator checks if the specified value exists anywhere in the list's elements:

CREATE TABLE test.list_table(
  id text,
  tags LIST<TEXT>,
  PRIMARY KEY((id))
);

-- Check if the tags list includes "important"
SELECT * FROM test.list_table WHERE id = 'post1' AND tags CONTAINS 'important';

2. Can SET types be used in secondary indexes? #

Yes! YCQL supports creating secondary indexes on SET columns. This lets you query for rows where the set contains a specific value without needing to specify the partition key (unlike the earlier query that required id = 1).

Example: Creating a secondary index on a SET column #

Using your original test.table_name table, create the index like this:

CREATE INDEX idx_ckk_set ON test.table_name(ckk);

Querying using the secondary index #

Once the index is created, you can run a query like this to find all rows (across all partitions) where ckk contains 4:

SELECT * FROM test.table_name WHERE ckk CONTAINS 4;

A quick heads-up: If your SET columns have a large number of unique elements, the index could grow large and impact write performance. As with any index, it's wise to evaluate your workload needs before creating it.

内容的提问来源于stack exchange，提问作者Ali Zeinali