Previous Next JavaScript must be enabled to correctly display this content

## SQLJSONExamples Script

The SQLJSONExample is available to illustrate JSON usage. This script creates the following table:

    create table if not exists JSONPersons (
      id integer,
      person JSON,
      primary key (id)
    );

The script imports the following table rows. Notice that the content for the `person` column, which is of type `JSON` contains a JSON object. That object contains a series of fields which represent our person. We have deliberately included inconsistent information in this example so as to illustrate how to handle various queries when working with JSON data.

    {
      "id":1,
      "person" : {
          "firstname":"David",
          "lastname":"Morrison",
          "age":25,
          "income":100000,
          "lastLogin" : "2016-10-29T18:43:59.8319",
          "address":{"street":"150 Route 2",
                     "city":"Antioch",
                     "state":"TN",
                     "zipcode" : 37013,
                     "phones":[{"type":"home", "areacode":423,
                                "number":8634379}]
                    },
          "connections":[2, 3],
          "expenses":{"food":1000, "gas":180}
      }
    }

    {
      "id":2,
      "person" : {
          "firstname":"John",
          "lastname":"Anderson",
          "age":35,
          "income":100000,
          "lastLogin" : "2016-11-28T13:01:11.2088",
          "address":{"street":"187 Hill Street",
                     "city":"Beloit",
                     "state":"WI",
                     "zipcode" : 53511,
                     "phones":[{"type":"home", "areacode":339,
                                "number":1684972}]
                    },
          "connections":[1, 3],
          "expenses":{"books":100, "food":1700, "travel":2100}
      }
    }

    {
      "id":3,
      "person" : {
          "firstname":"John",
          "lastname":"Morgan",
          "age":38,
          "income":100000000,
          "lastLogin" : "2016-11-29T08:21:35.4971",
          "address":{"street":"187 Aspen Drive",
                     "city":"Middleburg",
                     "state":"FL",
                     "phones":[{"type":"work", "areacode":305,
                                "number":1234079},
                               {"type":"home", "areacode":305,
                                "number":2066401}
                             ]
                    },
          "connections":[1, 4, 2],
          "expenses":{"food":2000, "travel":700, "gas":10}
      }
    }
    {
      "id":4,
      "person": {
          "firstname":"Peter",
          "lastname":"Smith",
          "age":38,
          "income":80000,
          "lastLogin" : "2016-10-19T09:18:05.5555",
          "address":{"street":"364 Mulberry Street",
                     "city":"Leominster",
                     "state":"MA",
                     "phones":[{"type":"work", "areacode":339,
                                "number":4120211},
                              {"type":"work", "areacode":339,
                               "number":8694021},
                              {"type":"home", "areacode":339,
                               "number":1205678},
                               null,
                              {"type":"home", "areacode":305,
                               "number":8064321}
                             ]
                    },
          "connections":[3, 5, 1, 2],
          "expenses":{"food":6000, "books":240, "clothes":2000,
                      "shoes":1200}
      }
    }

    {
      "id":5,
      "person" : {
          "firstname":"Dana",
          "lastname":"Scully",
          "age":47,
          "income":400000,
          "lastLogin" : "2016-11-08T09:16:46.3929",
          "address":{"street":"427 Linden Avenue",
                     "city":"Monroe Township",
                     "state":"NJ",
                     "phones":[{"type":"work", "areacode":201,
                                "number":3213267},
                              {"type":"work", "areacode":201,
                               "number":8765421},
                              {"type":"home", "areacode":339,
                               "number":3414578}
                             ]
                   },
          "connections":[2, 4, 1, 3],
          "expenses":{"food":900, "shoes":1000, "clothes":1500}
      }
    }

    {
      "id":6,
      "person" : {
          "mynumber":5,
          "myarray":[1,2,3,4]
      }
    }

    {
      "id":7,
      "person" : {
          "mynumber":"5",
          "myarray":["1","2","3","4"]
      }
    }

You run the SQLJSONExamples script using the load command:

    > cd <installdir>/examples/sql
    > java -jar <KVHOME>/lib/sql.jar -helper-hosts <host>:<port> \
    -store <storename> load \
    -file <KVHOME>/examples/sql/SQLJSONExamples.cli