JSON and DSE Search

By Berenguer Blasi -  October 28, 2016 | 0 Comments

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='*:*';

 id | date | season | solr_query | title              | trips
----+------+--------+------------+--------------------+-------
  1 | null |   Xmas |       null | First holiday ever |  null

(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='*:*';

 id | date | season | solr_query | title              | trips
----+------+--------+------------+--------------------+--------------------------------------------------------------------------------
  1 | null |   Xmas |       null | First holiday ever |                                                                           null
  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='*:*';

 id | date | season          | solr_query | title              | trips
----+------+-----------------+------------+--------------------+--------------------------------------------------------------------------------
  1 | null |            Xmas |       null | First holiday ever |                                                                           null
  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.









DataStax has many ways for you to advance in your career and knowledge.

You can take free classes, get certified, or read one of our many white papers.



register for classes

get certified

DBA's Guide to NoSQL







Comments

Your email address will not be published. Required fields are marked *




Subscribe for newsletter:

Tel. +1 (408) 933-3120 sales@datastax.com Offices France Germany

DataStax Enterprise is powered by the best distribution of Apache Cassandra™.

© 2017 DataStax, All Rights Reserved. DataStax, Titan, and TitanDB are registered trademark of DataStax, Inc. and its subsidiaries in the United States and/or other countries.
Apache Cassandra, Apache, Tomcat, Lucene, Solr, Hadoop, Spark, TinkerPop, and Cassandra are trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries.