You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cassandra.apache.org by Matteo Rulli <ma...@gmail.com> on 2016/02/10 15:00:26 UTC

Select values from map with multiple key values in where clause

Hello! 
I have a table like the following 

CREATE TABLE test (
    my_id uuid,
    values map<UUID, FROZEN <value_reference> >,
    PRIMARY KEY (my_id)
)...

and I would like to perform a query along these lines: 
SELECT * FROM test WHERE my_id = 670287fe-e080-42c3-9fae-7ffdc3309793 AND values CONTAINS KEY {00000000-0000-0000-0000-000000000001, 00000000-0000-0000-0000-000000000002};

Is this possible?

If the answer is no, the other approach I tried is via UDF. So the second question is: 

Is it possible to use UDF in where clause?

Reading the https://issues.apache.org/jira/browse/CASSANDRA-8488 <https://issues.apache.org/jira/browse/CASSANDRA-8488> issue I fear the answer is still no, right? 

As a last chance, I'm considering implementing a custom secondary index: is this a viable approach to get the kind of SELECT detailed above? 

Many thanks, 
matteo

Re: Select values from map with multiple key values in where clause

Posted by DuyHai Doan <do...@gmail.com>.

It's not possible have multiple keys in the CONTAINS KEY clause

Right now it is not possible to use UDF in WHERE clause, it may eventually
be possible one day

But you can use UDF in the Select clause to filter out data. In this case,
you'll need to wait for JIRA
https://issues.apache.org/jira/browse/CASSANDRA-10783 to get merged so that
you can pass literal value to UDF/UDA to filter data

No, don't go the way of secondary index unless you're very familiar with
Cassandra internals

On Wed, Feb 10, 2016 at 3:00 PM, Matteo Rulli <ma...@gmail.com>
wrote:

> Hello!
> I have a table like the following
>
> CREATE TABLE test (
>     my_id uuid,
>     values map<UUID, FROZEN <value_reference> >,
>     PRIMARY KEY (my_id)
> )...
>
>
> and I would like to perform a query along these lines:
>
> SELECT * FROM test WHERE my_id = 670287fe-e080-42c3-9fae-7ffdc3309793 AND values CONTAINS KEY {00000000-0000-0000-0000-000000000001, 00000000-0000-0000-0000-000000000002};
>
>
> *Is this possible?*
>
> If the answer is no, the other approach I tried is via UDF. So the second
> question is:
>
> *Is it possible to use UDF in where clause?*
>
> Reading the https://issues.apache.org/jira/browse/CASSANDRA-8488 issue I
> fear the answer is still no, right?
>
> As a last chance, I'm considering implementing a custom secondary index:
> is this a viable approach to get the kind of SELECT detailed above?
>
> Many thanks,
> matteo
>