cassandra 2.x cql 5 語法學習(二)

如何利用 create type insert multi json object

 example json data:

{ 
    username: 'user', 
    userage: 20,
    userprofile: {
        mail: {'user@abc.eamil.com', 'user1@abc.email.com'},
        phone: {1234567, 9876543},
        address: {      
                  street : 'Wu fu Rd.',
                  city : 'KAOHSIUNG CITY'
        }
    }    
}

1. create type address

cqlsh:demo>
CREATE TYPE address2 (
      street text,
      city text
  );

2. create type profile

cqlsh:demo>
CREATE TYPE profile (
      mail set<text>,
      phone set<int>,
      address address2 
  );

3. create type user_data for json

cqlsh:demo>
CREATE TYPE user_data (
      username text,
      userage int,
      userprofile profile
  );

4. create table user_profiles2

cqlsh:demo>  
  CREATE TABLE user_profiles2 (
      id int PRIMARY KEY,
      data user_data
  );

5. insert json data

cqlsh:demo>  
  INSERT INTO user_profiles2(id, data)
  VALUES (1,
         { 
            username: 'user', 
            userage: 20,
            userprofile: {
                mail: {'user@abc.eamil.com', 'user1@abc.email.com'},
                phone: {1234567, 9876543},
                address: {      
                    street : 'Wu fu Rd.',
                    city : 'KAOHSIUNG CITY'
                }    
            }
         }
  );          

6. select data

cqlsh:demo>  
select * from user_profiles2;


 id | data
----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {username: 'user', userage: 20, userprofile: {mail: {'user1@abc.email.com', 'user@abc.eamil.com'}, phone: {1234567, 9876543}, address: {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}}}

cqlsh:demo> 

or only grab address

cqlsh:demo>  
select data.userprofile.address from user_profiles2 where id = 1;


 data_userprofile_address
-----------------------------------------------
 {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}

cqlsh:demo>