question

somrcha1_186924 avatar image
somrcha1_186924 asked Erick Ramirez edited

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}');
data modelingcql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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