6

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.

Darwin Airola
  • 919
  • 8
  • 11

3 Answers3

6

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.

Tanvir
  • 1,453
  • 2
  • 16
  • 32
5

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.

Andy Tolbert
  • 11,418
  • 1
  • 30
  • 45
  • 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... – Darwin Airola Jul 13 '16 at 04:25
  • If you are concerned about replacing an existing record you can use 'INSERT INTO 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.
     – Andy Tolbert Jul 13 '16 at 17:13
  • Andy, I have not tested it, yet, but I believe that "INSERT INTO JSON 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
    SET ... ;". However, it would be nice to have the "UPDATE
    JSON ... ;" counterpart to "INSERT
    JSON ... ;".)
     – Darwin Airola Jul 14 '16 at 01:45
  • FYI, I have tested utilizing "INSERT INTO JSON ;" 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 SET = '', = '', ..., = '';" CQL statement...
     – Darwin Airola Jul 15 '16 at 00:22
0

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

Arpan Saini
  • 4,623
  • 1
  • 42
  • 50
  • 1
    Question was about using JSON object as update value. While your example is just regular update... – Alex Ott Feb 05 '19 at 21:41