Yugabyte YCQL check if a set contain a value?

Question

Is there there any way to query on a SET type(or MAP/LIST) to find does it contain a value or not?

Something like this:

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

Select * FROM table_name WHERE id = 1 AND ckk CONTAINS 4;

Is there any way to reach this query with YCQL api?

And can we use a SET type in SECONDRY INDEX?

score 2 · Answer 1 · answered Feb 10 '20 at 20:00

2

Is there any way to reach this query with YCQL api?

YCQL does not support the CONTAINS keyword yet (feel free to open an issue for this on the YugabyteDB GitHub). One workaround can be to use MAP<INT, BOOLEAN> instead of SET<INT> and the [] operator. For instance:

CREATE TABLE test.table_name(
    id text,
    ckk MAP<int, boolean>,
    PRIMARY KEY((id))
);
SELECT * FROM table_name WHERE id = 'foo' AND ckk[4] = true;

And can we use a SET type in SECONDRY INDEX?

Generally, collection types cannot be part of the primary key, or an index key. However, "frozen" collections (i.e. collections serialized into a single value internally) can actually be part of either primary key or index key.

For instance:

CREATE TABLE table2(
    id TEXT,
    ckk FROZEN<SET<INT>>,
    PRIMARY KEY((id))
) WITH transactions = {'enabled' : true};
CREATE INDEX table2_idx on table2(ckk);

answered Feb 10 '20 at 20:00

Mihnea Iancu

46
2

it seems using `Map` can solve my problem. but can i use Subscripted expression on a frozen map? – Ali Zeinali Feb 10 '20 at 20:18
@AliZeinali You can't use subscripted expression with a frozen map but you can with a normal map. Can you open an issue on github https://github.com/yugabyte/yugabyte-db/issues ? Why do you need a frozen map ? To use it as a primary key ? – dh YB Feb 11 '20 at 09:42
Lets say i have a table to store my posts and each post can have some tag (there is limit number of tags) then i want search for posts which contain some tags. This query most be fast so i need to create a secondary index on tags (by the same partition key as post pk) – Ali Zeinali Feb 11 '20 at 12:26

score 1 · Answer 2 · answered Feb 11 '20 at 10:50

1

Another option is to use with compound primary key and defining ckk as clustering key:

cqlsh> CREATE TABLE ybdemo.tt(id TEXT, ckk INT, PRIMARY KEY ((id), ckk)) WITH CLUSTERING ORDER BY (ckk DESC);
cqlsh> SELECT * FROM ybdemo.tt WHERE id='foo' AND ckk=4;

answered Feb 11 '20 at 10:50

dh YB

965
3
10

It's not what i'm looking for. ckk most be collection. And Repeating a row with different ckk would affect Write performance which i really don't want it :). – Ali Zeinali Feb 11 '20 at 12:34
How many values do you intend to save in 1 collection column ? – dh YB Feb 12 '20 at 14:32
Between 1 to 10 – Ali Zeinali Feb 13 '20 at 09:51
Are there other columns in the table ? – dh YB Feb 13 '20 at 10:11
Yes, It's a table which store messages of an instance messaging app. So there is other columns probably with large data like JSONB, TEXT, BLOB and others – Ali Zeinali Feb 13 '20 at 10:52
I suggest to create another table only with (pk,ckk) clustering column which can also be indexed and join the data in application side (on query time). I don't think write-performance will be very different. It's all key-values underneath https://docs.yugabyte.com/latest/architecture/docdb/persistence/#ycql-collection-type-example . – dh YB Feb 13 '20 at 14:59
1

Oh! I didn't know that. I wounder how i haven't seen this page before. – Ali Zeinali Feb 13 '20 at 19:02
If you want to make it more complex:: on the original table use a frozen, selecting the whole set will be fine, and maintain a secondary index manually. On insert, also upsert the rows. The same with update+delete. This can work with transactions. This helps so you don't have to do application side join when selecting messages. – dh YB Feb 15 '20 at 08:36

Yugabyte YCQL check if a set contain a value?

2 Answers2