Casssandra Select JSON

Apache Cassandra provides a native support for inserting and fetching the data from a table using JSON format. JSON is a popular and valuable data interchange format that is widely used in APIs and databases.

In this article, you will learn how to select rows from a Cassandra table in JSON format.

Creating Sample Data

Let us start by setting up a sample data for illustration purposes:

Create Keyspace

Before setting up any sample data, we need a database to store the data. We can do this by creating a sample keyspace.

The query is as provided in the following:

cqlsh> CREATE KEYSPACE store WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 3 };

The previous query creates a simple keyspace called store with a replication_factor of 3. If you are not familiar with Cassandra Keyspaces, check out our tutorial on the topic to learn more.

Once created, we can switch to the keyspace with the following command:

cqlsh> USE store;

Create Table

The next step is to create a table that holds the structure for our data. Keep in mind that Cassandra can handle unstructured data. However, for simplicity, we will attempt to use a table structure.

We can run the query as shown:

cqlsh:store> CREATE TABLE inventory ( product_id int, product_name text, quantity int, price int, availability boolean, PRIMARY KEY(product_id) );

The previous query should create an inventory table with the specified columns and data types.

cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability)
... VALUES (1, 'Meta Quest', 5, 399, true);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (2, 'Echo Dot', 10, 27, true);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (3, 'Tile Pro', 10, 26, false);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (4, 'Monitor', 100, 499, false);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (5, 'Laptop', 87, 700, true);

We can then query the table as:

SELECT * FROM inventory;

The previous query should return the records that are stored in the table as shown:

Query Data as JSON

To fetch the data from a table in JSON format, we can use the JSON keyword with the select statement as shown in the following syntax:

SELECT JSON column_names FROM table_name;

For example, to fetch the records from the inventory table as JSON, we can run the following query:

cqlsh:store> SELECT JSON * FROM inventory;

The previous query returns all the rows from the table as JSON.

Output:

[json]
----------------------------------------------------------------------
{"product_id": 5, "availability": true, "price": 700, "product_name": "Laptop", "quantity": 87}
{"product_id": 1, "availability": true, "price": 399, "product_name": "Meta Quest", "quantity": 5}
{"product_id": 2, "availability": true, "price": 27, "product_name": "Echo Dot", "quantity": 10}
{"product_id": 4, "availability": false, "price": 499, "product_name": "Monitor", "quantity": 100}
{"product_id": 3, "availability": false, "price": 26, "product_name": "Tile Pro", "quantity": 10}

(5 rows)

Conclusion

In this article, you learned how to use the JSON keyword in CQL to select the records from a table in JSON format. To learn how to select specific columns from a table as JSON, check our tutorial on the toJson() function.

Thanks for reading!

Casssandra Select JSON

Creating Sample Data

Query Data as JSON

Conclusion

About the author

John Otieno