CODEWITHSUNDEEP
pythoncassandra
Swesson
Swesson

Reputation: 109

Insert nested json into cassandra

Im trying to insert this nested object into cassandra database, but cannot figure out how to design the table for this. I want all the data from this object to be stored in cassandra.

Below I pasted the json im trying to insert.

Any suggestions?

{
  "status": "success",
  "data": {
    "resultType": "vector",
    "result": [
      {
        "metric": {
          "__name__": "up",
          "env": "demosite",
          "instance": "localhost:9100",
          "job": "node"
        },
        "value": [
          1553849977.349,
          "1"
        ]
      },
      {
        "metric": {
          "__name__": "up",
          "instance": "ub-lab-server:9090",
          "job": "prometheus"
        },
        "value": [
          1553849977.349,
          "1"
        ]
      }
    ]
  }
}

Upvotes: 7

Views: 3251

Answers (1)

Rambler
Rambler

Reputation: 5490

There are multiple ways to do this.

If your use case is just to store the JSON as a string , just serialize the whole JSON as a blob into a single column Create a table as : 

CREATE TABLE IF NOT EXISTS my_table(
  ID <text/bigint>
  DATA text,
  CREATEDATE timestamp
)

If you want to represent all the nested attributes as separate columns , you will have start from bottom up. i.e first create User Defined Types for each nested field. In reference to your json , an example would be : 

CREATE TYPE metric (
      name text,
      env text,
      instance text,
      job text
);

CREATE TYPE value(
      field1 text,
      field2 text
);

Once you have the bas UDTs created , start with creating Composite UDTs , referencing base UDTs : 

CREATE TYPE result(
  metric metric,
  value value,
);

CREATE TYPE data(
  resultType text,
  result set<result>,
);

And then finally bootstrap your table as following : 

CREATE TABLE IF NOT EXISTS my_table (
  ID <text/bigint>
  status data
)

Be careful in deciding on your primary/partition keys based on your querying and storage usecases.

Upvotes: 5

Related Questions