Is there an elegant way to do JSON updates via CQL (Cassandra)?

Question

I would (ideally) want to update the string in Cassandra using pure JSON (for example, similar to the CQL expression INSERT INTO <table_name> JSON '<json_object_string>'; ). However, such functionality does not seem to exist using the CQL UPDATE statement.

One (brain dead) approach that I was considering was to remove and then reinsert the corresponding row. However, this approach definitely has its drawbacks - thus eliminating it from my consideration.

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

Answer 1

In Kassandra, INSERT and UPDATE are the same operation. There is no UPDATE function to support Cassandra json.

There is also no partial support for JSON updates, i.e. After inserting a row, you cannot update individual columns using JSON, since any columns skipped from the json payload are inserted as zeros (gravestones). However, you can use regular INSERT / UPDATE queries.

CASSANDRA-11424 strives to solve this problem.

Answer 2

Functionality is now available in CQL 5.1 . Syntax:

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

The DEFAULT UNSET allows DEFAULT UNSET to overwrite only the values ​​found in the JSON string. So, for example, if you had other columns (e.g. column3, column4) with data in the record, these columns will retain their original data when the insert statement is executed above.

Answer 3

You can execute the query below to update the JSON column in Casandra CQL

 update <schema_name>.<table_name> set <COLUMN_NAME> = {amount : 4.2,iso_code: 'USD'} WHERE <COLUMN1> = <VALUE1> and <COLUMN2> = <VALUE2>; 

Replace = with values ​​according to your needs

Remember: the key names will be without double quotes, and the value will be in single quotes, as shown in the example above