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.

Lapides

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.

Midi

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.

Squeamish

Andy, I just determined that INSERT also does an UPDATE when developing my tests. For my current application, this (INSERT does an UPDATE) behavior might be a bit problematic if there turn out to be cases when INSERT should only INSERT if the key/value pair does not exist. In that case, it seems that I would need to read in order to determine whether or not the key/value pair exists before allowing the INSERT. Ugh, in that case... – Lapides

If you are concerned about replacing an existing record you can use 'INSERT INTO <table> JSON .... IF NOT EXISTS;'. There will be a performance penalty for that, but it may be worth it and is more appropriate than read before write. – Squeamish

Andy, I have not tested it, yet, but I believe that "INSERT INTO <table> JSON <JSON_object_string> IF NOT EXISTS;" will not update fields that have changed. The ideal would be to update the fields that have changed and to leave the specified fields alone. (I seem to have that functionality working with "UPDATE <table> SET ... ;". However, it would be nice to have the "UPDATE <table> JSON ... ;" counterpart to "INSERT <table> JSON ... ;".) – Lapides

FYI, I have tested utilizing "INSERT INTO <table> JSON <JSON_object_string>;" as an UPDATE operation, but it is just an INSERT operation. Thus, right now, it appears that the only way to do an update is via the "UPDATE <table_name> SET <key1> = '<new_value1>', <key2> = '<new_value2>', ..., <keyN> = '<new_valueN>';" CQL statement... – Lapides

With INSERT I cannot use conditions, like I can with UPDATE. – Melan

Answer 3

You can follow the below query to update a 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 as per your needs

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

Bhutan

Question was about using JSON object as update value. While your example is just regular update... – Hazaki