CODEWITHSUNDEEP
cassandracql
demo999 89
demo999 89

Reputation: 105

Insert data in map<text,text> in cassandra db

i have a column in cassandra database as map<text,text>

I insert the data in this table as :

INSERT INTO "Table1" (col1) VALUES ({'abc':'abc','hello':'world','flag':'true'});

So, in my code i can get the data as :

{
    "abc":"abc",
    "hello":"world",
    "flag":"true"
}

But, now i want this like :

{
    "abc":"abc",
    "hello":"world",
    "flag":{
    "data":{ "hi":"cassandra"},
    "working":"no"
    }
}

For this, when I try the insert query, it says that it does not match the type map<text,text>

How can I make this work ?

Upvotes: 2

Views: 6824

Answers (1)

Aaron
Aaron

Reputation: 57748

The problem here (in your second example) is that the type of col1 is a map<text,text> but flag is a complex type and no longer matches that definition. One way to solve this would be to create individual TEXT columns for each property, as well as a user defined type for flag and the data it contains:

> CREATE TYPE flagtype (data map<text,text>,working text);
> CREATE TABLE table1 (abc text,
                     hello text,
                      flag frozen<flagtype>
                     PRIMARY KEY (abc));

Then INSERTing the JSON text from your second example works.

> INSERT INTO table1 JSON '{"abc":"abc",
                          "hello":"world",
                           "flag":{"data":{"hi":"cassandra"},
                                      "working":"no"}}';

> SELECT * FROM table1;

 abc | flag                                       | hello
-----+--------------------------------------------+-------
 abc | {data: {'hi': 'cassandra'}, working: 'no'} | world

(1 rows)

If you are stuck on using the map<text,text> type, and want the value JSON sub properties to be treated a large text string, you could try a simple table like this:

CREATE TABLE stackoverflow.table2 (
  key1 text PRIMARY KEY,
  col1 map<text, text>);

And on your INSERTs just escape out the inner quotes:

> INSERT INTO table2 JSON '{"key1":"1","col1":{"abc":"abc","hello":"world"}}';
> INSERT INTO table2 JSON '{"key1":"2","col1":{"abc":"abc","hello":"world",
                  "flag":"{\"data\":{\"hi\":\"cassandra\"},\"working\":\"no\"}"}}';

> SELECT * FROm table2;

 key1 | col1
------+----------------------------------------------------------------------------------------
    2 | {'abc': 'abc', 'flag': '{"data":{"hi":"cassandra"},"working":"no"}', 'hello': 'world'}
    1 |                                                       {'abc': 'abc', 'hello': 'world'}

(2 rows)

That's a little hacky and will probably require some additional parsing on your application side. But it gets you around the problem of having to define each column.

Upvotes: 3

Related Questions