CODEWITHSUNDEEP
jsoncassandra
user3470294
user3470294

Reputation: 190

Cassandra - Nested Json Insert

DSE 6.7 CentOS 6.7

We have data in JSON with the below format -

{ "Event_Type": "Booked", "Car": { "RegId": "0961dbac-297d-424c-96ce-9f37f1707cd9" } }

{ "Event_Type": "Complete", "Car": { "RegId": "0961dbac-297d-424c-96ce-9f37f1707cd9" } }

{ "Event_Type": "Booked", "Car": { "RegId": "b32f7b0b-d077-44c4-a454-519a3f82c759" } }

{ "Event_Type": "Complete", "Car": {"RegId": "b32f7b0b-d077-44c4-a454-519a3f82c759"} }

{ "Event_Type": "Booked", "Car": { "RegId": "6fa0b439-0782-49e8-9ac3-78c275470516" } }

{ "Event_Type": "Complete", "Car": { "RegId": "6fa0b439-0782-49e8-9ac3-78c275470516"} }

we need to insert into cassandra with the below table structure

CREATE TABLE IF NOT EXISTS "car"."car_appt" (

"event_type" text,

"RegId" uuid,

"timetamp" timestamp

);

Requried Output

Event_Type      RegId

Booked          0961dbac-297d-424c-96ce-9f37f1707cd9
Complete        0961dbac-297d-424c-96ce-9f37f1707cd9
Booked          b32f7b0b-d077-44c4-a454-519a3f82c759
Complete        b32f7b0b-d077-44c4-a454-519a3f82c759

I am unable to insert the above json, since it is inside a nested json.

Let me know if I need to provide more information.

Upvotes: 1

Views: 165

Answers (1)

Jim Wartnick
Jim Wartnick

Reputation: 2206

Cassandra tries to put some structure around a non-structured row that you've supplied. You didn't specify "car", which is really what your reqId "belongs to", so it bombs. The best option is to create a type (so it can be used with the PK definition) and then create the table in the following way:

create type cartype (   
  regId text
);

create table car_appt (
  event_type text, 
  car frozen<cartype>, 
  timestamp timestamp, 
  primary key (event_type, car)
);

Once that's done, you're all set.

insert into car_appt JSON '{ "Event_Type": "Booked", "Car": { "RegId": "6fa0b439-0782-49e8-9ac3-78c275470516" } }';


select * from car_appt;



event_type | car
------------+-------------------------------------------------
     Booked | {regid: '6fa0b439-0782-49e8-9ac3-78c275470516'}

Also

select car.regId from car_appt;



car.regid
--------------------------------------
 6fa0b439-0782-49e8-9ac3-78c275470516

Like I said, this creates some structure around your data, where-as json itself is unstructured. This means you can't have some "rows" that have different fields in the nested document as it will fail. You need to have "car" and you need to have "regId" for all cassandra rows or it will fail. If this doesn't seem to work well for you, you may want to consider a document database instead (i.e. Mongo).

From your update, here is the output of the 6 inserts you provided. Other than the order, and the regid column heading, it looks the same.

select event_type, car.regId from car_appt;

 event_type | car.regid
------------+--------------------------------------
     Booked | 0961dbac-297d-424c-96ce-9f37f1707cd9
     Booked | 6fa0b439-0782-49e8-9ac3-78c275470516
     Booked | b32f7b0b-d077-44c4-a454-519a3f82c759
   Complete | 0961dbac-297d-424c-96ce-9f37f1707cd9
   Complete | 6fa0b439-0782-49e8-9ac3-78c275470516
   Complete | b32f7b0b-d077-44c4-a454-519a3f82c759

Upvotes: 1

Related Questions