Indexing JSON Fields

You can create an index on a JSON field. To create the index, specify it as you would any other index, except that you must define the data type of the JSON field you are indexing.

Note that there are some restrictions on the data type of the JSON field that you can index. See JSON Indexes for more information.

To create the index, first create the table:

CREATE Table JSONPersons (
    id INTEGER,
    person JSON,
    PRIMARY KEY (id)
) 

To create the index, you must specify the JSON field to be indexed using dot notation. Suppose your table rows look like this:

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

Then once the table has been added to the store, you can create an index for one of the JSON fields like this:

CREATE INDEX idx_json_income on JSONPersons (person.income AS integer)

To retrieve data using a JSON index, you first retrieve the index using its name, and create an instance of IndexKey that you will use to perform the index lookup. The following is used to retrieve all table rows where the person.income field is 100000:

Index jsonIndex = myTable.getIndex("idx_json_income");
IndexKey indexKey = jsonIndex.createIndexKey(); 
indexKey.put("person.income", 100000); 

When you perform the index lookup, the only rows returned will be those which have a JSON field with the specified field value. You then retrieve the matching table rows, and iterate over them in the same way you would any other index type. For example:

TableIterator<Row> iter = tableH.tableIterator(indexKey, null, null);
System.out.println("Results for person.income, value 100000: ");
try {
    while (iter.hasNext()) {
        Row rowRet = iter.next();
        int id = rowRet.get("id").asInteger().get();
        System.out.println("id: " + id);
        MapValue mapRet = rowRet.get("person").asMap();
        System.out.println("person: " + mapRet.toString());
    }
} finally {
    if (iter != null) {
        iter.close();
    }
} 

For a more complete example of using JSON data fields, including a JSON index, see JSON By Example.