JSON format in Cassandra

In this article, we will discuss JSON JavaScript Object Notation format to insert data into the table and it is useful and inserting columns and its values programmatically are more practical than using cqlsh. For testing and verifying queries cqlsh is very convenient. Cassandra Query Language (CQL) supported JSON format in which data is inserted as a string but in actual is stored as a given data type value. For example, if Id has int data type then the value of id will be inserted like “Id”: “101” but in actual it will be stored as an INT. Let’s consider a table student_registration in which s_id, s_name, s_city, s_email are the fields.

CREATE TABLE student_registration(
s_id int PRIMARY KEY, 
s_name text, 
s_city text, 
s_email text
); 

Now, here we will insert data into the student_registration table in JSON format. Let’s have a look.

INSERT INTO student_registration
JSON '{
"s_id" : "9001",
"s_name" : "Ashish",
"s_city" : "California",
"s_email": "[email protected]"
}';


INSERT INTO student_registration
JSON '{
"s_id" : "9002",
"s_name" : "Ashish",
"s_email": "[email protected]"
}' ;

INSERT INTO student_registration
JSON '{
"s_id" : "9003",
"s_name" : "Rana",
"s_email": "[email protected]"
}' ; 

Let's see the output using the following CQL query.

select * 
from student_registration; 

Output: Here, s_city value is not inserted using JSON so, it is by default NULL value will be inserted. nested JSON: Let's understand with an example for nested JSON first we are creating user defined type permanent_address which has house_no, name, city, pin are the field.

CREATE type permanent_address
( 
house_no int, 
name text, 
city text,
pin int 
); 

Now, here we are creating registration_form table for registration purpose which has student_id, name, registration_fees, and permanent_address are the fields.

CREATE TABLE registration_form( 
"student_id" int  PRIMARY KEY, 
"name" text, 
"registration_fees" int, 
"permanent_address" list<frozen>); 

Now, here to insert data into table used the following CQL query.

INSERT INTO registration_form("student_id", "name",  
                                   "registration_fees", 
                                   "permanent_address") 
VALUES (123, 'Ashish', 2500, 
 [{ house_no : 544, name : 'ashish', city : 'california', pin : 2019 },
  { house_no : 124, name : 'rana', city : 'delhi', pin : 2020}]) ; 

Let's see the inserted data by using the following CQL query.

select * 
from registration_form; 

Output: toJson() is JSON function to convert a JSON string. Let's have a look.

 select student_id, toJson(permanent_address) 
from registration_form; 

Output: Reference - DataStax