Cassandra ToJSON

JavaScript Object Notation or JSON format is one of the modern age’s most versatile and popular data-interchange formats. JSON is easy to read and parse for both humans and machines. It also provides small file sizes, allowing for use even in embedded devices.

For such reasons, JSON has become the de facto choice for various use cases including APIs, databases, and more.

In this tutorial, we will learn how to use Cassandra’s built-in JSON features to retrieve the records from a table in JSON format.

Let’s dive in.

Create Sample Data

Before discussing how we can retrieve the data as JSON in Cassandra, let us set up some sample data for illustration purposes.

Start by creating a keyspace:

CREATE KEYSPACE linuxhint WITH REPLICATION = {'class': 'SimpleStrategy', 'replication_factor': 3};

Next, switch the keyspace as:

USE linuxhint;

We can then create a table and add a sample data as shown:

Create a sample table:

cqlsh:linuxhint> CREATE TABLE users( id int, username text, email text, active int, PRIMARY KEY(id));

Add a sample data:

cqlsh:linuxhint> CREATE TABLE users( id int, username text, email text, active int, PRIMARY KEY(id));
Add sample data as shown:
cqlsh:linuxhint> INSERT INTO users(id, username, email, active) VALUES (0, 'user1', '[email protected]', 1);
cqlsh:linuxhint> INSERT INTO users(id, username, email, active) VALUES (1, 'user2', '[email protected]', 1);
cqlsh:linuxhint> INSERT INTO users(id, username, email, active) VALUES (2, 'user3', '[email protected]', 0);
cqlsh:linuxhint> INSERT INTO users(id, username, email, active) VALUES (3, 'user4', '[email protected]', 0);
cqlsh:linuxhint> INSERT INTO users(id, username, email, active) VALUES (4, 'user5', '[email protected]', 1);

We can query the table as shown:

cqlsh:linuxhint> SELECT * FROM users;

The resulting table is as shown:

Select All Records as JSON

In Cassandra, we can add the JSON keyword in a select statement to fetch all the records from a table in JSON format.

The command syntax is as shown:

SELECT JSON column_names FROM table_name;

For example:

cqlsh:linuxhint> SELECT JSON * FROM users;

The previous command returns the records of the users’ table as JSON.

[json]
-----------------------------------------------------------------------
{"id": 1, "active": 1, "email": "[email protected]", "username": "user2"}
{"id": 0, "active": 1, "email": "[email protected]", "username": "user1"}
{"id": 2, "active": 0, "email": "[email protected]", "username": "user3"}
{"id": 4, "active": 1, "email": "[email protected]", "username": "user5"}
{"id": 3, "active": 0, "email": "[email protected]", "username": "user4"}

(5 rows)

Select Specific Columns as JSON

In most cases, you will want to fetch the specific columns as JSON. Cassandra provides us with the tojson function to accomplish this.

The command syntax is as shown:

SELECT column_names toJson(column_names) FROM table_name;

For example, to return the username column as JSON, we can run the following command:

cqlsh:linuxhint> SELECT id, active, email, username, toJson(username) FROM users;

The previous command should return the username column as JSON. An example output is as shown:

Keep in mind that the function accepts only one column at a time.

Conclusion

In this tutorial, we discussed how we could use the built-in JSON functionality in Cassandra to retrieve the records in JSON format.

Thanks for reading!

Cassandra ToJSON

Create Sample Data

Select All Records as JSON

Select Specific Columns as JSON

Conclusion

About the author

John Otieno