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"
        }
    ]
    }
);