Is there an elegant way to perform a JSON update via CQL (Cassandra)?

Question

I would (ideally) like to update a row in Cassandra utilizing pure JSON (e.g., analogous to the "INSERT INTO <table_name> JSON '<json_object_string>';" CQL statement). However, it does not appear that such functionality exists via the CQL UPDATE statement.

One (brain dead) approach that I considered was to delete and then reinsert the relevant row. However, that approach definitely has its drawbacks -- thus, eliminating it from my consideration set.

I implemented a version utilizing the "UPDATE <table_name> SET <key1> = '<new_value1>', <key2> = '<new_value2>', ..., <keyN> = '<new_valueN>';" CQL statement. However, if there were something like "UPDATE <table_name> JSON '<new_json_object_string>';", I would really like to know about that.

Answer 1

The functionality is now available in CQL 5.1. The syntax is:

INSERT INTO table_name JSON '{"column1": "value1", "column2": "value2"}' DEFAULT UNSET;

The DEFAULT UNSET option makes it only overwrite values found in the JSON string. So, for example, if you had other columns (e.g. column3, column4) with data in the record, those columns will retain their original data when the insert statement above is run.

Answer 2

In cassandra, INSERT and UPDATE are the same operation. For Cassandra's json support, there is no UPDATE capability.

There is also no partial JSON update support, i.e. after inserting a row, you can't update individual columns using JSON as any columns ommitted from the json payload are inserted as nulls (tombstones). You can however use the regular INSERT/UPDATE queries.

CASSANDRA-11424 seeks to resolve this.