cql - How to select from the Cassandra using JSON child object in a column?

Question

Below is how an entry looks like in the Cassandra:

id | address                     | age | family  | name | siblings
-----------------------------------------------------------------
 1 | {'city': 'c1', 'rue': 'r1'} |  23 |      si |   si | {'b', 'd'}

I want to select based on the city which is a child of JSON in the address column:

I am not sure if the following are the correct statements:

select address from Persons address.city='f';  
select address.city from Persons ;   

question from:https://stackoverflow.com/questions/65849926/how-to-select-from-the-cassandra-using-json-child-object-in-a-column

Answer 1

Unfortunately, that cannot be done in Cassandra (at least for now).

One possible option is to change your model to something like

 id | city | rue | age | family  | name | siblings
----+------+-----+-------------------------------
  1 |   c1 |  r1 |  23 |      si |   si | {'b', 'd'}

then you will be able to execute query:

select json city, rue from addresses where city = 'c1';  

to achieve "ready-to-use" json in result:

 [json]
-----------------------------
{"city": "c1", "rue": "r1"}