Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

somrcha1_186924 avatar image
somrcha1_186924 asked ·

Is it possible to store data in JSON or XML format in the same column?

Hi,

Is there a possibility to insert formatted JSON and XML in same coulmn?

Application team want to save log as column value.Logs can be in XML or JSON and they are looking for the possibility.

e.g.

cassandra@cqlsh:omsdb> Create TABLE omsdb.EVENT(Id int PRIMARY KEY,Event_data text);
cassandra@cqlsh:omsdb> INSERT INTO omsdb.EVENT(Id, EVENT_DATA) VALUES (1,'<note> </note>');
cassandra@cqlsh:omsdb> select * from EVENT;
id | event_data
----+----------------
1 | <note> </note>

Is that possible to insert JSON here?

I am facing error to insert query

INSERT INTO omsdb.EVENT(Id, EVENT_DATA) VALUES (2,'{productid: 5545, item : 'wire', amount : 200}');
json
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered ·

Yes, it is possible to store strings in both JSON or XML format in a CQL text column. However, it isn't related to the issue you have.

Cause

The reason you're having problems with inserting the data are:

  1. You have used the same single-quotes (') within the same string to quote 'wire' which means your CQL statement is malformed.
  2. Your JSON data is invalid and you need to make sure it is formatted correctly.

Example

To illustrate, here is the schema of my sample table I created on DataStax Astra:

CREATE KEYSPACE community WITH replication = {'class': 'NetworkTopologyStrategy', 'dc-1': '1'};

CREATE TABLE community.events (
    id int PRIMARY KEY,
    event text
);

Here are the statements for inserting both XML- and JSON-formatted strings:

token@cqlsh:community> INSERT INTO events (id, event) VALUES (1, '<tag>random text</tag>');
token@cqlsh:community> INSERT INTO events (id, event) VALUES (2, '{"k1":"some string", "k2":100}');

The result:

token@cqlsh:community> SELECT * FROM events ;

 id | event
----+--------------------------------
  1 |         <tag>random text</tag>
  2 | {"k1":"some string", "k2":100}

Solution

Here is a valid JSON representation of your data:

{
   "productid":5545,
   "item":"wire",
   "amount":200
}

With a valid JSON, you should be able to insert the data:

token@cqlsh:community> INSERT INTO events (id, event) VALUES (3, '{"productid":5545, "item":"wire", "amount":200 }');

Again, here is the result:

token@cqlsh:community> SELECT * FROM events WHERE id = 3;

 id | event
----+-------------------------------------------------
  3 | {"productid":5545, "item":"wire", "amount":200}

Cheers!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.