Working with JSON data

DataStax DevCenter is compatible with DataStax Enterprise (DSE) versions up to 5.0.

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:

CREATE TABLE IF NOT EXISTS social.users 
(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 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 complex.phone (alias text, number text);

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

CREATE TABLE IF NOT EXISTS complex.users 
(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"}]}}'));