Sample Data

Our sample data is contained in person_contacts.json. We use it create a simple two-column table to hold our JSON data.

The first column is an account ID, and it serves as the primary key. At a minimum, every table will always have a single non-JSON field that serves as the primary key. If you wish to use compound primary keys, or one or more shard keys, then the number of non-JSON fields will expand.

Our second field is a JSON field. Like all such fields, it can contain any valid JSON data. This provides extreme flexibility for your table schema, which is particularly useful when it is necessary to evolve your data's schema. However, it comes at the cost of requiring more error checking to ensure that your JSON contains the data you expect it to contain.

CREATE TABLE personContacts (account INTEGER,
                             person JSON,
                             PRIMARY KEY(account)) 

We load this table with 23 rows, some of which are deliberately incomplete. Each row is represented as a single JSON object. We show a representative section of the sample data file, below, for your reference.

In the following listing, notice that Account 3 only provides a work address — there is no home address. Account 4 provides no address information at all. Account 5 fails to provide any data at all for the person field. Account 22 explicitly sets the address object to null. Account 23 explicitly sets both the home and work addresses to null. All of this is valid JSON and all of it should be handled by our code.

{
"account" : 1,
"person" : {
      "lastName" : "Jones",
      "firstName" : "Joe",
      "address" : {
        "home" : {
          "street" : "15 Elm",
          "city" : "Lakeville",
          "zip" : "12345"
        },
        "work" : {
          "street" : "12 Main",
          "city" : "Lakeville",
          "zip" : "12345"
        }
      },
      "phone" : {
        "home" : "800-555-1234",
       "work" : "877-123-4567"
      }
   }
}

{
"account" : 2,
"person" : {
      "lastName" : "Anderson",
      "firstName" : "Nick",
      "address" : {
        "home" : {
          "street" : "4032 Kenwood Drive",
          "city" : "Boston",
          "zip" : "02102"
        },
        "work" : {
          "street" : "541 Bronx Street",
          "city" : "Boston",
          "zip" : "02102"
        }
      },
      "phone" : {
        "home" : "800-555-9201",
       "work" : "877-123-8811"
      }
   }
}

{
"account" : 3,
"person" : {
      "lastName" : "Long",
      "firstName" : "Betty",
      "address" : {
        "work" : {
          "street" : "10 Circle Drive",
          "city" : "Minneapolis",
          "zip" : "55111"
        }
      },
      "phone" : {
        "home" : "800-555-2701",
       "work" : "877-181-4912"
      }
   }
}

{
"account" : 4,
"person" : {
      "lastName" : "Brown",
      "firstName" : "Harrison",
      "phone" : {
        "home" : "800-555-3838",
       "work" : "877-753-4110"
      }
   }
}

{
"account" : 5
}

{
"account" : 6,
"person" : {
      "lastName" : "Abrams",
      "firstName" : "Cynthia",
      "address" : {
        "home" : {
          "street" : "2 Fairfield Drive",
          "city" : "San Jose",
          "zip" : "95054"
        }
      },
      "phone" : {
        "home" : "800-528-4897",
       "work" : "877-180-5287"
      }
   }
}

# ...
# sample data removed for the book. See person_contact.json
# in ..../examples/table for the complete data
# file.
# ...

{
"account" : 21,
"person" : {
      "lastName" : "Blase",
      "firstName" : "Lisa",
      "address" : {
        "home" : {
          "street" : "72 Rutland Circle",
          "city" : "Boston",
          "zip" : "02102"
        },
        "work" : {
          "street" : "541 Bronx Street",
          "city" : "Boston",
          "zip" : "02102"
        }
      },
      "phone" : {
        "home" : "800-555-4404",
       "work" : "877-123-2277"
      }
   }
}

{
"account" : 22,
"person" : {
      "address" : null,
      "phone" : {
        "home" : "800-555-1234",
       "work" : "877-123-4567"
      }
   }
}

{
"account" : 23,
"person" : {
      "address" : {
        "home" : null,
        "work" : null 
      },
      "phone" : {
        "home" : "800-555-1234",
       "work" : "877-123-4567"
      }
   }
}