JSON and DSE Search

JSON is a popular format. This post provides information on easy ways to use JSON with DSE Search.

Some approaches to using JSON with DSE Search use FIT (Field Input Transformers) and other complex methods. These methods are valid, but there are easier ways of doing things which cover most cases. This demo uses DSE 5.0.3. Let’s see an example:

Set-up for the demo

1. Start by creating a CQL table:

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

USE jsondemo;

create type jsondemo.trip (

origin text,

dest text,

);

create table jsondemo.holidays (

"id" VARCHAR PRIMARY KEY,

"title" TEXT,

"season" TEXT,

"date" date,

"trips" list<frozen<trip>>);

2. Now create a DSE Search core against that table

dsetool create_core jsondemo.holidays generateResources=true

Inserting JSON

A. Simple JSON String: Notice how we can feed our JSON directly to Apache Cassandra™ and DSE picks it up and indexes it. No need to do any preprocessing or exploding of the JSON string

cqlsh> insert into jsondemo.holidays JSON '{"id":"1", "title":"First holiday ever", "season": "Xmas"}';

cqlsh> select * from jsondemo.holidays where solr_query='*:*';

----+------+--------+------------+--------------------+-------

(1 rows)

B. Tuple JSON-like approach: When working with a Tuple or a UDT you can insert them with a JSON-like approach and keep the rest of the fields like in standard CQL statements. This approach is useful when the rest of the row fields, besides Tuple/UDTs, are not available in JSON.

cqlsh> insert into jsondemo.holidays (id, title, season, trips) values ('2', 'Week in Barcelona', 'Easter', [{origin: 'London', dest:'Barcelona'}, {origin: 'Barcelona', dest:'London'}]);

cqlsh> select * from jsondemo.holidays where solr_query='*:*';

----+------+--------+------------+--------------------+--------------------------------------------------------------------------------

2 | null | Easter | null | Week in Barcelona | [{origin: 'London', dest: 'Barcelona'}, {origin: 'Barcelona', dest: 'London'}]

(2 rows)

C. Full JSON: Tuple/UDTS can be inserted if all your fields are available as JSON.

cqlsh> insert into jsondemo.holidays JSON '{"id":"3", "title":"Week in Miami", "season": "Summer holidays", "trips": [{"origin": "Barcelona", "dest": "Miami"}, {"origin": "Miami", "dest": "Barcelona"}]}';

cqlsh> select * from jsondemo.holidays where solr_query='*:*';

----+------+-----------------+------------+--------------------+--------------------------------------------------------------------------------

2 | null | Easter | null | Week in Barcelona | [{origin: 'London', dest: 'Barcelona'}, {origin: 'Barcelona', dest: 'London'}]

3 | null | Summer holidays | null | Week in Miami | [{origin: 'Barcelona', dest: 'Miami'}, {origin: 'Miami', dest: 'Barcelona'}]

(3 rows)

Querying for JSON

It is equally easy to get your results back as JSON.

cqlsh> select json * from jsondemo.holidays where solr_query='*:*';

[json]

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

{"id": "1", "date": null, "season": "Xmas", "solr_query": null, "title": "First holiday ever", "trips": null}

{"id": "2", "date": null, "season": "Easter", "solr_query": null, "title": "Week in Barcelona", "trips": [{"origin": "London", "dest": "Barcelona"}, {"origin": "Barcelona", "dest": "London"}]}

{"id": "3", "date": null, "season": "Summer holidays", "solr_query": null, "title": "Week in Miami", "trips": [{"origin": "Barcelona", "dest": "Miami"}, {"origin": "Miami", "dest": "Barcelona"}]}

(3 rows)

Conclusions

There is no need to use FIT and other elaborate techniques to use JSON in DSE. Complex approaches like having a field holding the full JSON string, to be later exploded into individual fields so they are each indexed etc are not necessary. DSE provides out of the box functionality that supports JSON in most cases.