user1 user1 - 15 days ago 9
JSON Question

How to parse JSON value of a text column in cassandra

I have a column of text type be contain JSON value.

{
"customer": [
{
"details": {
"customer1": {
"name": "john",
"addresses": {
"address1": {
"line1": "xyz",
"line2": "pqr"
},
"address2": {
"line1": "abc",
"line2": "efg"
}
}
}
"customer2": {
"name": "robin",
"addresses": {
"address1": null
}
}
}
}
]
}


How can I extract 'address1' JSON field of column with query?

First I am trying to fetch JSON value then I will go with parsing.

SELECT JSON customer from text_column;


With my query, I get following error.


com.datastax.driver.core.exceptions.SyntaxError: line 1:12 no viable
alternative at input 'customer' (SELECT [JSON] customer...)

com.datastax.driver.core.exceptions.SyntaxError: line 1:12 no viable
alternative at input 'customer' (SELECT [JSON] customer...)


Cassandra version 2.1.13

Answer

You can't use SELECT JSON in Cassandra v2.1.x CQL v3.2.x

For Cassandra v2.1.x CQL v3.2.x :

The only supported operation after SELECT are :

  • DISTINCT
  • COUNT (*)
  • COUNT (1)
  • column_name AS new_name
  • WRITETIME (column_name)
  • TTL (column_name)
  • dateOf(), now(), minTimeuuid(), maxTimeuuid(), unixTimestampOf(), typeAsBlob() and blobAsType()

In Cassandra v2.2.x CQL v3.3.x Introduce : SELECT JSON

With SELECT statements, the new JSON keyword can be used to return each row as a single JSON encoded map. The remainder of the SELECT statment behavior is the same.

The result map keys are the same as the column names in a normal result set. For example, a statement like “SELECT JSON a, ttl(b) FROM ...” would result in a map with keys "a" and "ttl(b)". However, this is one notable exception: for symmetry with INSERT JSON behavior, case-sensitive column names with upper-case letters will be surrounded with double quotes. For example, “SELECT JSON myColumn FROM ...” would result in a map key "\"myColumn\"" (note the escaped quotes).

The map values will JSON-encoded representations (as described below) of the result set values.