Machine Learning & Big Data Blog

Using JSON with Cassandra

Walker Rowe
by Walker Rowe
2 minute read

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 };

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}]

Wikibon: Automate your Big Data pipeline

Learn how data management experts throughout the industry are transforming their Big Data infrastructure for maximum business impact.
Download Now ›

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.

About the author

Walker Rowe

Walker Rowe

Walker Rowe is an American freelance tech writer and programmer living in Tunisia. He specializes in big data, analytics, and programming languages. Find him on LinkedIn or Upwork.