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