Machine Learning & Big Data Blog

Using JSON with Cassandra

2 minute read
Walker Rowe

Cassandra provides support for JSON. You can, of course, store JSON text into Cassandra text columns. But that is not what we mean here. Here we mean you can use JSON to write to Cassandra tables.

We illustrate with an example. First create a keyspace.

create keyspace json with REPLICATION = {'class' : 'SimpleStrategy', 'replication_factor' : 2 };

(This article is part of our Cassandra Guide. Use the right-hand menu to navigate.)

Simple JSON Example

Let’s make a table with three columns. Then write data into it using the JSON keyword. That tells Cassandra to maps the JSON values to the columns names, just as you would expect with JSON.

create table books (isbn text primary key, title text, publisher text);
insert into books JSON '{"isbn": "123", 
"title": "The Magic Mountain",
"publisher": "Knofp"}';

Then we list the data. It looks like regular columnar data and not JSON. Because it’s just regular data. We simply used JSON to add it, which is useful since data is often represented as JSON.

select * from books;
isbn | publisher | title
------+-----------+--------------------
123 |     Knofp | The Magic Mountain

Nested JSON

Here is a more complicated example. We have a customer record with a sales type that is of type list. Obviously a customer would have multiple sales. So this is a good way to represent that.

First we create the sale type. Then we create the customers table. We have to use the FROZEN keyword as each sales transaction cannot be changed, i.e., it is immutable. If you want to change a sales transaction you would have to delete it and then add it back.

use json;
CREATE type json.sale ( id int, item text, amount int );
CREATE TABLE json.customers ( id int  PRIMARY KEY, name text, balance int, sales list> );

Now add values. Since the sales items are a list we can add more than one.

INSERT INTO json.customers (id, name, balance, sales) 
VALUES (123, 'Greenville Hardware', 700,
[{ id: 5544, item : 'tape', amount : 100},
{ id: 5545, item : 'wire', amount : 200}]) ;

Now list the items. Note that there is no question mark around the key value in the jSON. This is because it is a JSON object.

select * from customers;
id  | balance | name                | sales
-----+---------+---------------------+--------------------------------------------------------------------------------
123 |     700 | Greenville Hardware | [{id: 5544, item: 'tape', amount: 100}, {id: 5545, item: 'wire', amount: 200}]

If you want to convert it to a JSON string then use toJson()

select id, toJson(sales) from customers;
id  | system.tojson(sales)
-----+--------------------------------------------------------------------------------------------
123 | [{"id": 5544, "item": "tape", "amount": 100}, {"id": 5545, "item": "wire", "amount": 200}]

Learn ML with our free downloadable guide

This e-book teaches machine learning in the simplest way possible. This book is for managers, programmers, directors – and anyone else who wants to learn machine learning. We start with very basic stats and algebra and build upon that.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Walker Rowe

Walker Rowe is an American freelancer tech writer and programmer living in Cyprus. He writes tutorials on analytics and big data and specializes in documenting SDKs and APIs. He is the founder of the Hypatia Academy Cyprus, an online school to teach secondary school children programming. You can find Walker here and here.