Working with JSON data

JSON is supported in INSERT and SELECT statements in the CQL Script editor.

You can INSERT and SELECT data from tables with the data represented as JSON objects. The members of the object map to the column names in the table. For example:

With this schema:

(id uuid PRIMARY KEY, name text);

And this data:

INSERT INTO social.users JSON '{ "id" : "93031620-12ae-11e4-9191-0800200c9a66", "name" : "Gary Binary"}';

INSERT INTO social.users JSON '{ "id" : "f6071e72-48ec-4fcb-bf3e-379c8a696488", "name" : "Jo Quux"}';

SELECT JSON * FROM social.users;

When working with results in JSON format, DevCenter displays them in a new Results viewer that support syntax highlighting and code folding (using the +/- signs). You can also copy data as JSON directly from the Results panel.

CQL Script editor JSON support

The CQL Script editor supports the following when editing JSON-specific statements:
  • new CQL keywords and features
  • code assist
  • syntax highlighting
  • validation
  • quick fixes
For example, code assist below:

The CQL Script editor also supports the toJson and fromJson functions.

The toJson function is only valid in the selection clause of a SELECT statement. For example, given the following schema and some data:

CREATE TYPE (alias text, number text);

CREATE TYPE complex.address (street text, city text, zip_code int, phones list<FROZEN<phone>>);

(id uuid PRIMARY KEY, name text, addresses map<text, FROZEN<address>>);

INSERT INTO complex.users (id, name, addresses) 
VALUES (756716f7-2e54-4715-9f00-91dcbea6cf50, 'John Doe', 
{ 'home': { street : '1021 West 4th St. #202',
            city : 'San Pedro',
            zip_code : 92330,
            phones : [ { alias : 'home', number : '213-555-1212' } ] } } );

INSERT INTO complex.users (id, name, addresses) 
VALUES (f6071e72-48ec-4fcb-bf3e-379c8a696488, 'Jane Quux', 
{ 'home': { street : '2580 Arnold Dr.',
            city : 'San Fransisco',
            zip_code : 94110,
            phones : [ { alias : 'home', number : '415-555-8945' } ] } } );

INSERT INTO complex.users (id, name, addresses) 
VALUES (93031620-12ae-11e4-9191-0800200c9a66, 'Gary Binary', 
{ 'home': { street : '123 Eddy St.',
            city : 'Petaluma',
            zip_code : 95566,
            phones : [ { alias : 'home', number : '707-555-2323' } ] } } );

Retrieve the addresses column back as JSON data:

SELECT name, toJson(addresses) FROM complex.users;

In the Results tab:

And, in the Details data viewer:

The fromJson function is valid in INSERT, UPDATE, and DELETE statements. Using the same schema as above, an example of an INSERT statement:

INSERT INTO complex.users (id, name, addresses)
VALUES (d7b9b58a-2edc-11e5-a151-feff819cdc9f, 'Berthold Boxcart', 
	fromJson('{"home":{"street":"12 Ghort St.","city":"Graton","zip_code":95444,"phones":[{"alias":"mobile","number":"707-555-8452"}]}}'));