Increment Counter in a Map

In YCQL how can we increment a counter inside a MAP?

For example: UPDATE a SET m['k'] = m['k'] + 1 WHERE...
The usual increment/decrement operation for a counter in the map produces this error Operator not allowed as right hand value

For Collection Datatypes map/list/set, + and - operator is used to append or remove the <key, value> entries from the collection. Also, we cannot use counter datatype in collections, every time you try to update counter will just be re-initialized.

UPDATE counter_test SET mapcounter['c1'] = + 1 WHERE id = 'a1';
id | mapcounter ----+------------ a1 | {'c1': 1}

UPDATE counter_test SET mapcounter['c1'] = + 2 WHERE id = 'a1';
id | mapcounter ----+------------ a1 | {'c1': 2}

You will need to handle the counter increment on the application side.

Please refer to docs here -

  1. https://docs.yugabyte.com/latest/api/ycql/type_collection/#collection-expressions
  2. https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlUpdate.html#cqlUpdate__assignment
1 Like

@llKetanll
At the moment map['key'] is not supported as a right-hand-side expression in CQL (YCQL or Apache Cassandra).
For example, in Apache Cassandra the test like this gives the following error:

cqlsh:k> update t1 set m['k'] = 3 where id=1;
cqlsh:k> select * from t1;
 id | m
----+----------
  1 | {'k': 3}
(1 rows)
cqlsh:k> update t1 set m['k'] = m['k'] where id=1;
SyntaxException: line 1:24 no viable alternative at input '[' (... set m['k'] = [m][...)
cqlsh:k> update t1 set m['k'] = m['k']+1 where id=1;
SyntaxException: line 1:24 no viable alternative at input '[' (... set m['k'] = [m][...)

As an alternative way you can use common columns (with using of the map-key as a clustering column for better performance):

ycqlsh:k> create table t2 (id int, m_key text, m_val int, primary key((id), m_key));
ycqlsh:k> insert into t2 (id, m_key, m_val) values (100, 'k', 1);
ycqlsh:k> select * from t2;
 id  | m_key | m_val
-----+-------+-------
 100 |     k |     1
(1 rows)
ycqlsh:k> update t2 set m_val = m_val + 1 where id = 100 and m_key = 'k';
ycqlsh:k> select * from t2;
 id  | m_key | m_val
-----+-------+-------
 100 |     k |     2
(1 rows)