JSON Support in ScyllaDB

Beginning with version 2.3, ScyllaDB Open Source NoSQL Database supports the Javascript Object Notation (JSON) format. That includes inserting JSON documents, retrieving data in JSON and providing helper functions to transform native CQL types into JSON and vice versa.

Also note that schemas are still enforced for all operations — one cannot just insert random JSON documents into a table. The new API is simply a convenient way of working with JSON without having to convert everything back and forth client-side.

JSON support consists of CQL statements and functions, described here, one by one, with examples.

You can use the following code snippet to build a sample restaurant menu. This example will serve as a basis in the following sections. This snippet also contains a second table based on collections, which contains additional information about served dishes.

CREATE KEYSPACE restaurant WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
use restaurant;

CREATE TABLE menu (category text, position int, name text, price float, PRIMARY KEY(category, position));

INSERT INTO menu (category, position, name, price) VALUES ('starters', 1, 'foie gras', 10.50);
INSERT INTO menu (category, position, name, price) VALUES ('starters', 2, 'steak tartare', 9.50);
INSERT INTO menu (category, position, name, price) VALUES ('starters', 3, 'taco de pulpo', 8.00);
INSERT INTO menu (category, position, name, price) VALUES ('soups', 1, 'sour rye soup', 12);
INSERT INTO menu (category, position, name, price) VALUES ('soups', 2, 'sorrel soup', 8);
INSERT INTO menu (category, position, name, price) VALUES ('soups', 3, 'beef tripe soup', 11.20);
INSERT INTO menu (category, position, name, price) VALUES ('main courses', 1, 'red-braised pork belly', 24.90);
INSERT INTO menu (category, position, name, price) VALUES ('main courses', 2, 'boknafisk', 19);

CREATE TABLE info (category text PRIMARY KEY, calories map<text, int>, vegan set, ranking list);
INSERT INTO info (category, calories, vegan, ranking) VALUES ('soups', {'sour rye soup': 500, 'sorrel soup': 290}, {'sorrel soup'}, ['sour rye soup', 'sorrel soup']);

SELECT JSON

Selecting data in JSON format can be performed with SELECT JSON statement. It’s syntax is almost identical to regular CQL SELECT.

In order to extract all data and see what the restaurant serves, try:

SELECT JSON * from menu;

Named columns can also be specified to narrow down the results. So, if we’re only interested in names and prices:

SELECT JSON name, price from menu;

As in regular CQL SELECT, it’s of course possible to restrict the query. Extracting soup info from the database can be achieved like this:

SELECT JSON name, price from menu WHERE category='soups';

Since data underneath is still structured with our schema, it’s possible to apply filtering too. So, if our meal is reimbursed anyway and we don’t want to ruin it by spending too little money:

SELECT JSON name, price from menu WHERE price > 10 ALLOW FILTERING;

Note that the results always consist of one column named [json]. This column contains the requested information in JSON format, properly typed – to string, int, float or boolean. Of course, (nested) collections are supported too!

SELECT JSON * FROM info;

INSERT JSON

Inserting JSON data is also very similar to a regular INSERT statement. Still, note that even though JSON documents can contain lots of arbitrary columns, the ones inserted into ScyllaDB will be validated with table’s schema. Let’s add another soup to the menu:

INSERT INTO menu JSON '{"category": "soups", "position": 4, "name": "red borscht", "price": 11}';

That’s it – not complicated at all. What happens if we try to sneak some out-of-schema data to the statement?

INSERT INTO menu JSON '{"category": "soups", "position": 4, "name": "red borscht", "price": 11, "comment": "filling and delicious"}';

Not possible – schema rules cannot be ignored. What if some columns are missing from our JSON?

INSERT INTO menu JSON '{"category": "soups", "position": 4, "price": 16}'

SELECT * from menu;

Works fine, the omitted column just defaults to null. But, there’s more to the topic.

DEFAULT NULL/DEFAULT UNSET

By default, omitted columns are going to be treated as null values. If, instead, the user wants to omit changing the value in case it already exists, DEFAULT UNSET flag can be used. So, if our red borscht sells well and we want to boost the price in order to increase revenue:

INSERT INTO menu JSON '{"category": "soups", "position": 4, "price": 16}' DEFAULT UNSET;

We can see that our soup name was left intact, but the price changed:

SELECT * FROM menu WHERE category='soups';

fromJson()

fromJson() is a functional equivalent of INSERT JSON for a single value. The easiest way to explain its usage is with an example:

INSERT INTO menu (category, position, name, price) VALUES (fromJson('”soups”'), fromJson(‘1’), 'sour rye soup', 12);

The function works fine with collections too.

INSERT INTO info (category, calories) VALUES ('starters', fromJson('{"foie gras": 550}'));

SELECT * FROM info WHERE category = 'starters';

toJson()

toJson() is a counterpart of the fromJson() function (yes, really!) and can be used to convert single values to JSON format.

SELECT toJson(category), toJson(name) FROM menu;

SELECT category, toJson(calories), toJson(vegan), toJson(ranking) FROM info;

Types

Mapping of CQL types to JSON is well defined and usually intuitive. Full reference table of corresponding types can be found below. Note that some CQL types (e.g. decimal) will be implicitly converted to others, with possibly different precision (e.g. float) when returning JSON values.