Cassandra fromJSON()

Cassandra is a versatile database with tons of feature and support for modern features. One such feature is JSON. Cassandra allows you to export or import data from JSON objects.

In this post, we will discuss how we can insert data from JSON into a Cassandra database.

Sample Keyspace and Table

Let us start by creating a sample keyspace and table. This will allow us to insert sample data from JSON format.

You can use the code as shown below:

create keyspace store
with replication = {
'class': 'SimpleStrategy',
'replication_factor': 1};

Once we have the target keyspace, we can switch to it and create a table as shown:

cqlsh> USE store ;
cqlsh:store> create table products( product_id uuid, product_name text, price int, primary key(product_id));

The command above will create a table called products holding columns product_id, product_name, and price.

We can then craft the JSON data we wish to insert into the table. The sample JSON is as shown:

{
"product_id": "a872710c-39ee-4f34-964a-843a73eb0032",
"product_name": "Product one",
"price": 100
}

We can insert the above JSON data using the INSERT INTO command as shown:

cqlsh:store> insert into store.products json
'{
"product_id": "a872710c-39ee-4f34-964a-843a73eb0032",
"product_name": "Product one",
"price": 100
}';

In the example code above, we start by calling the INSERT INTO command followed by the name of the table. We then use the JSON keyword and enclose the JSON data inside single quotes.

This should insert the data into the table as specified by the columns and values. We can confirm by fetching the data.

cqlsh:store> select * from store.products ;

The query above should return the data as shown:

product_id | price | product_name
--------------------------------------+-------+--------------
a872710c-39ee-4f34-964a-843a73eb0032 | 100 | Product one
(1 rows)

Note that although we insert the product_id value as a string, Cassandra will parse and store it as a UUID value.

If the value of a given column is not provided, Cassandra will insert a NULL value in its place.

An example is as shown:

cqlsh:store> insert into store.products json '{ "product_id": "350eb980-1ee3-4892-84d2-88eab97cc71f", "product_name": "Product three" }';

Note that in this case, we omit the price column. Cassandra will replace the value with NULL as shown:

cqlsh:store> select * from store.products ;

Resulting output:

Conclusion

In this article, you learned how to insert JSON data into a Cassandra table using CQLSH commands.

Cassandra fromJSON()

Sample Keyspace and Table

Resulting output:

Conclusion

About the author

John Otieno