SQLJSONExamplesスクリプト

SQLJSONExampleは、JSONの使用方法を説明するために用意されています。このスクリプトにより、次の表が作成されます。

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

このスクリプトでは、次の表の行がインポートされます。JSONタイプのperson列の内容にはJSONオブジェクトが含まれていることに注意してください。そのオブジェクトには、個人を表す一連のフィールドが含まれています。この例では、JSONデータを使用する際の様々な問合せの使用方法を説明するために、意図的に一貫性のない情報を含めています。

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

loadコマンドを使用して、SQLJSONExamplesスクリプトを実行します。

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