Hello Maxim.
I think you won't be able to do what you want this way. Collections are
supposed to be (ideally small) sets of data that you'll always read
entirely, at once. At least it seems to be working this way. Not sure about
the latest versions, but I did not hear about new design for collections.
You can set values individually in a collection as you did above (and
probably should do so to avoid massive tombstones creation), but you have
to read the whole thing at once:
```
$ ccm node1 cqlsh -e "SELECT items[10] FROM tlp_labs.products WHERE
product_id=1;"
<stdin>:1:SyntaxException: line 1:12 no viable alternative at input '['
(SELECT [items][...)
$ ccm node1 cqlsh -e "SELECT items FROM tlp_labs.products WHERE
product_id=1;"
items
--------------------------------------------------------------------
{10: {csn: 100, name: 'item100'}, 20: {csn: 200, name: 'item200'}}
```
Furthermore, you cannot query the TTL for a single item in a collection,
and as distinct columns can have distinct TTLs, you cannot query the TTL
for the whole map (collection). As you cannot get the TTL for the whole
thing, nor query a single item of the collection, I guess there is no way
to get the currently set TTL for all or part of a collection.
If you need it, you would need to redesign this table, maybe split it. Make
the collection a different table for example, that you would then be
referenced in your current table.
Another idea of hack I'm just thinking about could be to add a 'ttl' field
that would get the updates as well, any time a client updates the TTL for
an entry, you could update that 'ttl' field as well. But again, you would
still not be able to query this information only for an item or a few, it
would be querying the whole map again.
I had to test it because I could not remember about this, and I think my
observations are making sense. Sadly, there is no 'good' syntax for this
query, it's just not permitted at all I would say. Sorry I have no better
news for you :).
C*heers,
-----------------------
Alain Rodriguez - al...@thelastpickle.com
France / Spain
The Last Pickle - Apache Cassandra Consulting
http://www.thelastpickle.com
Le mer. 19 juin 2019 à 09:21, Maxim Parkachov <lazy.gop...@gmail.com> a
écrit :
> Hi everyone,
>
> I'm struggling to understand how can I query TTL on the row in collection
> ( Cassandra 3.11.4 ).
> Here is my schema:
>
> CREATE TYPE item (
> csn bigint,
> name text
> );
>
> CREATE TABLE products (
> product_id bigint PRIMARY KEY,
> items map<bigint, frozen<item>>
> );
>
> And I'm creating records with TTL like this:
>
> UPDATE products USING TTL 10 SET items = items + {10: {csn: 100, name:
> 'item100'}} WHERE product_id = 1;
> UPDATE products USING TTL 20 SET items = items + {20: {csn: 200, name:
> 'item200'}} WHERE product_id = 1;
>
> As expected first records disappears after 10 seconds and the second after
> 20. But if I already have data in the table I could not figure out how to
> query TTL on the item value:
>
> SELECT TTL(items) FROM products WHERE product_id=1;
> InvalidRequest: Error from server: code=2200 [Invalid query]
> message="Cannot use selection function ttl on collections"
>
> SELECT TTL(items[10]) FROM products WHERE product_id=1;
> SyntaxException: line 1:16 mismatched input '[' expecting ')' (SELECT
> TTL(items[[]...)
>
> Any tips, hints, tricks are highly appreciated,
> Maxim.
>
