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

Question

question

somrcha1_186924 asked Dec 2, '20 Erick Ramirez answered Dec 2, '20

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

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

Answer 1 · 2020-12-02T23:59:59Z

Erick Ramirez answered Dec 2, '20

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:

You have used the same single-quotes (') within the same string to quote 'wire' which means your CQL statement is malformed.
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

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

question