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.

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 Scylla will be validated with table’s schema. Let’s add another soup to the 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:

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.

CQL type

INSERT JSON accepted type

SELECT JSON returned type

ascii

string

string

bigint

integer, string

integer

blob

string

string

boolean

boolean, string

boolean

date

string

string

decimal

integer, string, float

float

double

integer, string, float

float

float

integer, string, float

float

inet

string

string

int

integer, string

integer

list

list, string

list

map

map, string

map

smallint

integer, string

integer

set

list, string

list

text

string

string

time

string

string

timestamp

integer, string

string

timeuuid

string

string

tinyint

integer, string

integer

tuple

list, string

list

uuid

string

string

varchar

string

string

varint

integer, string

integer

We do JSON. How about you?

JSON support in Scylla permits a variety of new novel designs and implementations. If you are currently using JSON in your own Scylla deployment or planning to use this feature in your own development, we’d love to hear from you.

ScyllaDB

Keep in Touch

Apache®, Apache Cassandra®, are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.