Appendix
The following code creates the UserInfo
table.
CREATE TABLE UserInfo (id INTEGER, uname STRING, info JSON, PRIMARY KEY(id));
The following code populates the UsersInfo
table with sample rows.
INSERT INTO UserInfo VALUES (
1001,
"Peter",
{
"age":42,
"income":65000,
"address": {
"street":"Lane-8",
"city":"Boston",
"state":"MA",
"phones":[
{"area":415,"number":91237468,"kind":"work"},
{"area":null,"number":95213607,"kind":"home"}
]
},
"vehicles" : [
{
"vid":72132,
"vtype":"car",
"vpass":[
{"passid":396457,"issuedby":"BPD"},
{"passid":312358,"issuedby":"NYPD"}
],
"vservice":[
{"serviceid":20001,"servicedate":null}
]
},
{
"vid":78344,
"vtype":"bike",
"vpass":[
{"passid":396241,"issuedby":"BPD"}
]
}
],
"expenses":{"housing":1000,"clothes":230,"books":20},
"connections":[100,20,20,10,20]
}
);
INSERT INTO UserInfo VALUES (
1002,
"Ram",
{
"age":35,
"income":null,
"address":{
"street":"Hosur Road",
"city":"Bengaluru",
"state":"KA",
"phones":[
{"area":080,"number":2653457,"kind":"work"},
{"area":080,"number":2659753,"kind":"home"}
]
},
"vehicles":null,
"expenses":{"housing":1000,"travel":300},
"connections":[ ]
}
);
INSERT INTO UserInfo VALUES (
1003,
"Alice",
{
"income":20000,
"address":{
"street":"Fremont Rd",
"city":"San Jose",
"state":"CA",
"phones":[ ]
},
"expenses":null,
"connections":null
}
);
INSERT INTO UserInfo VALUES (1004,"Chan",{});
INSERT INTO UserInfo VALUES (
1005,
"John",
{
"age":60,
"address":{
"street":"Taylor Blvd",
"city":"San Fransisco",
"state":"CA",
"phones":{"area":408,"number":50,"kind":"work"}
},
"expenses":{"housing":1000,"travel":300},
"connections":[30,5,null]
}
);
INSERT INTO UserInfo VALUES (
1006,
"Cathy",
{
"address":{
"street":"26th Avenue",
"city":"Chennai",
"state":"TN"
},
"vehicles":[
{
"vid":98642,
"vtype":"bike"
}
]
}
);