Using JSON
The JSON datatype cannot be used as part of a primary or shard key.
To define a simple two-field table where the primary key is a UID and the second field contains a JSON data field, you use the following DDL statement:
CREATE TABLE myJsonTable (
uid INTEGER,
myJSON JSON,
PRIMARY KEY (uid)
)
The data that you write for this datatype can be any valid JSON stored as a string. For example, all of the following are valid:
final String jsonNumber = "2";
final String jsonString = "\"a json string\"";
final String jsonObject_null = "{}";
final String jsonObject = "{\"a\": 1.006, \"b\": null," +
"\"bool\" : true, \"map\": {\"m1\": 5}," +
"\"ar\" : [1,2.7,3]}";
final String jsonNull = "null";
To store a JSON value in the table that we defined, above:
TableAPI tableH = kvstore.getTableAPI();
Table myJsonTable = tableH.getTable("myJsonTable");
Row row = myTable.createRow();
row.put("uid", 12345);
String jsonArray="[1,5,11.1,88]";
row.putJson("myJSON", jsonArray);
tableH.put(row, null, null);
To retrieve it:
TableAPI tableH = kvstore.getTableAPI();
Table myTable = tableH.getTable("myJsonTable");
PrimaryKey pkey = myTable.createPrimaryKey();
pkey.put("uid", 12345);
Row row = tableH.get(pkey, null);
int uid = row.get("uid").asInteger().get();
String jsonStr = row.get("myJSON").toString();
System.out.println("uid: " + uid + " JSON: " + jsonStr);
Be aware that a version of Row.putJson()
exists that allows you to use Java Readers to stream JSON data from I/O locations (such as files on disk). For example, to stream a small file from disk use java.io.FileReader
:
TableAPI tableH = kvstore.getTableAPI();
Table myTable = tableH.getTable("myJsonTable");
Row row = myTable.createRow();
row.put("uid", 666);
try {
FileReader fr = new FileReader("myJsonFile.txt");
row.putJson("myJson", fr);
tableH.put(row, null, null);
} catch (FileNotFoundException fnfe) {
System.out.println("File not found: " + fnfe);
}
For a more complete example of using JSON data fields, see JSON By Example.