CODEWITHSUNDEEP
cassandra
veera prabu
veera prabu

Reputation: 31

Setting default unset value for user defined data type fields in cassandra

I am storing nested JSON values in Cassandra,Since I have lot fields, is there way to keep unset values for the fields not coming in

Consider I have a Table like below,

CREATE TYPE address (number int, street text, address2 text);

CREATE TABLE users (
    id int PRIMARY KEY,
            street_address frozen<address>
);

INSERT INTO users JSON '{"id": 5, "street_address": 
   {"number": 123, "street": "Main St."}}';

If I ran the above query below is the column value,

5   {number:123,street:'Main St.',address2:NULL}

I want to ignore this address2:NULL insertion in 2nd column. I tried "default unset" also but it is not working

I just framed with the 2 fields in this table, similiar to I have multiple fields with nested JSON objects. Is there any workaround to avoid null insertion

Upvotes: 1

Views: 2619

Answers (1)

Alex Ott
Alex Ott

Reputation: 87164

Answer after updated question (Update on 15.10.2019: updated answer is incorrect because of the copy-paste error in this example (users vs users2)! - it looks like that when updating from JSON, the UDT is replaced completely):

You're using the frozen UDT here, and it's updated at once (as one binary object), so if the field is missing in data then it's represented as null. But if you're using non-frozen UDT, then DEFAULT UNSET works as required - the missing field doesn't overwrite existing value:

cqlsh:test> INSERT INTO users2 JSON '{"id": 5, "street_address": {"number": 123, 
    "street": "Main St.", "address2": "test" }}';
cqlsh:test> SELECT * from users2;

 id | street_address
----+-----------------------------------------------------
  5 | {number: 123, street: 'Main St.', address2: 'test'}

(1 rows)
cqlsh:test> INSERT INTO users JSON '{"id": 5, "street_address": {"number": 1234, 
    "street": "Main St.3"}}' DEFAULT UNSET ;
cqlsh:test> SELECT * from users2;

 id | street_address
----+-----------------------------------------------------
  5 | {number: 123, street: 'Main St.', address2: 'test'}

(1 rows)

First variant of the answer:

By default, JSON is inserted with DEFAULT NULL option specified - this means that all missing fields are replaced with null, so they can delete existing data. If you want to have them unset, then you need to specify corresponding option after the JSON value, like this:

INSERT INTO mytable JSON '{ "\"myKey\"": 0, "value": 0}' DEFAULT UNSET;

See Cassandra documentation for clarification on this topic.

P.S. If I remember correctly, this option may not exist in every Cassandra version.

Upvotes: 1

Related Questions