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.