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
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 -
@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)