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.