Blog

Aerospike Document API: JSONPath Queries

headshot-Roi-Menashe
Roi Menashe
Software Engineer
July 7, 2021|7 min read

The Aerospike Document API is a new project that allows for Aerospike Collection Data Type (CDT) objects to be accessed and mutated using a JSONPath syntax.

A few weeks ago my colleague Ken Tune introduced the project in his Medium article “Aerospike Document API”. You can find the code on GitHub at aerospike/aerospike-document-lib.

In this article we will skip past the basics to demonstrate the power of using JSONPath queries with Aerospike.

JSONPath Queries

After combining the initial Document API library with the Jayway JsonPath library, we can now query documents stored in Aerospike bins using JSONPath operators, functions and filters.

The idea is basic; we send a JSONPath query to Aerospike, specifying the relevant key and the bin name that stores the document, and get back the data matching the query. The syntax that cannot be directly supported by Aerospike is split. CDT operations are used to grab what is possible from Aerospike, then the JsonPath library is used to post-process this result. You can also put, delete and append items at a path matching a JSONPath query.

document-api-eaf2070a865d42d1d051694a2540db2a

Consider the following document:

{
  "store": {
    "book": [
      {
        "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95,
        "ref": [1,2]
      },
      {
        "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99,
        "ref": [2,4,16]
      },
      {
        "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99,
        "ref": [1,3,5]
      },
      {
        "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99,
        "ref": [1,2,7]
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  },
  "expensive": 10
}

Let’s assume we already have this document stored in Aerospike under a bin called “documentBin” (saving a JSON in Aerospike is simple and covered in the Medium article mentioned above).

Now let’s see some queries examples:

We will use the following key and bin name:

String aerospikeKey = new Key("test", "test-set", "jsonExampleKey");
String documentBinName = "documentBin";

1. Get all products, both books and bicycles

// Get all products, both books and bicycles
String jsonPath = "$.store.*";
Object objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

objectFromDB = {HashMap@2349}  size = 2
 "bicycle" -> {HashMap@2357}  size = 2
  key = "bicycle"
  value = {HashMap@2357}  size = 2
   "color" -> "red"
    key = "color"
    value = "red"
   "price" -> {Double@2446} 19.95
    key = "price"
    value = {Double@2446} 19.95
 "book" -> {ArrayList@2359}  size = 4
  key = "book"
  value = {ArrayList@2359}  size = 4
   0 = {HashMap@2361}  size = 5
    "ref" -> {ArrayList@2373}  size = 2
     key = "ref"
     value = {ArrayList@2373}  size = 2
      0 = {Long@2448} 1
      1 = {Long@2449} 2
    "category" -> "reference"
    "title" -> "Sayings of the Century"
    "author" -> "Nigel Rees"
    "price" -> {Double@2381} 8.95
   1 = {HashMap@2362}  size = 5
    "ref" -> {ArrayList@2390}  size = 3
     key = "ref"
     value = {ArrayList@2390}  size = 3
      0 = {Long@2449} 2
      1 = {Long@2455} 4
      2 = {Long@2456} 16
    "category" -> "fiction"
    "title" -> "Sword of Honour"
    "author" -> "Evelyn Waugh"
    "price" -> {Double@2398} 12.99
   2 = {HashMap@2363}  size = 6
    "ref" -> {ArrayList@2408}  size = 3
     key = "ref"
     value = {ArrayList@2408}  size = 3
      0 = {Long@2448} 1
      1 = {Long@2452} 3
      2 = {Long@2453} 5
    "category" -> "fiction"
    "title" -> "Moby Dick"
    "author" -> "Herman Melville"
    "price" -> {Double@2416} 8.99
    "isbn" -> "0-553-21311-3"
   3 = {HashMap@2364}  size = 6
    "ref" -> {ArrayList@2428}  size = 3
     key = "ref"
     value = {ArrayList@2428}  size = 3
      0 = {Long@2448} 1
      1 = {Long@2449} 2
      2 = {Long@2450} 7
    "category" -> "fiction"
    "title" -> "The Lord of the Rings"
    "author" -> "J. R. R. Tolkien"
    "price" -> {Double@2436} 22.99
    "isbn" -> "0-395-19395-8"

2. Get the authors of all books

// Get the authors of all books
String jsonPath = "$.store.book[*].author";
Object objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

objectFromDB = {JSONArray@2526} size = 4
 0 = “Nigel Rees”
 1 = “Evelyn Waugh”
 2 = “Herman Melville”
 3 =J. R. R. Tolkien”

3. Modify the authors of all books to “J.K. Rowling”

// Get the authors of all books
String jsonPath = "$.store.book[*].author";
String jsonObject = "J.K. Rowling";
// Modify the authors of all books to "J.K. Rowling"
documentClient.put(aerospikeKey, documentBinName, jsonPath, jsonObject);
Object objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

All of the books author’s field will be modified to “J.K. Rowling” in the Aerospike database, we can see it by querying the authors of all books.

objectFromDB = {JSONArray@2536}  size = 4
 0 = "J.K. Rowling"
 1 = "J.K. Rowling"
 2 = "J.K. Rowling"
 3 = "J.K. Rowling"

4. Get all the books with an ISBN number

// Get all the books with an ISBN number
jsonPath = "$..book[?(@.isbn)]";
objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

objectFromDB = {JSONArray@2562}  size = 2
 0 = {LinkedHashMap@2578}  size = 6
  "ref" -> {JSONArray@2589}  size = 3
   key = "ref"
   value = {JSONArray@2589}  size = 3
    0 = {Integer@2601} 1
    1 = {Integer@2602} 3
    2 = {Integer@2603} 5
  "category" -> "fiction"
  "title" -> "Moby Dick"
  "author" -> "Herman Melville"
  "price" -> {Double@2597} 8.99
  "isbn" -> "0-553-21311-3"
 1 = {LinkedHashMap@2579}  size = 6
  "ref" -> {JSONArray@2613}  size = 3
   key = "ref"
   value = {JSONArray@2613}  size = 3
    0 = {Integer@2601} 1
    1 = {Integer@2625} 2
    2 = {Integer@2626} 7
  "category" -> "fiction"
  "title" -> "The Lord of the Rings"
  "author" -> "J. R. R. Tolkien"
  "price" -> {Double@2621} 22.99
  "isbn" -> "0-395-19395-8"

5. Get all the books in store cheaper than 10

// Get all the books in store cheaper than 10
jsonPath = "$.store.book[?(@.price < 10)]";
objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

objectFromDB = {JSONArray@2580}  size = 2
 0 = {LinkedHashMap@2597}  size = 5
  "ref" -> {JSONArray@2607}  size = 2
   key = "ref"
   value = {JSONArray@2607}  size = 2
    0 = {Integer@2617} 1
    1 = {Integer@2618} 2
  "category" -> "reference"
  "title" -> "Sayings of the Century"
  "author" -> "Nigel Rees"
  "price" -> {Double@2615} 8.95
 1 = {LinkedHashMap@2598}  size = 6
  "ref" -> {JSONArray@2628}  size = 3
   key = "ref"
   value = {JSONArray@2628}  size = 3
    0 = {Integer@2617} 1
    1 = {Integer@2640} 3
    2 = {Integer@2641} 5
  "category" -> "fiction"
  "title" -> "Moby Dick"
  "author" -> "Herman Melville"
  "price" -> {Double@2636} 8.99
  "isbn" -> "0-553-21311-3"

6. Get all the books matching regex (ignore case)

// Get all the books matching regex (ignore case)
jsonPath = "$..book[?(@.author =~ /.*REES/i)]";
objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

objectFromDB = {JSONArray@2585}  size = 1
 0 = {LinkedHashMap@2602}  size = 5
  "ref" -> {JSONArray@2611}  size = 2
   key = "ref"
   value = {JSONArray@2611}  size = 2
    0 = {Integer@2621} 1
    1 = {Integer@2622} 2
  "category" -> "reference"
  "title" -> "Sayings of the Century"
  "author" -> "Nigel Rees"
  "price" -> {Double@2619} 8.95

7. Delete the price field of every object exists in store

// Get the price of everything
String jsonPath = "$.store..price";
// Delete the price field of every object exists in the store
documentClient.delete(aerospikeKey, documentBinName, jsonPath);
Object objectFromDB = documentClient.get(aerospikeKey, documentBinName, jsonPath);

Results:

All of the price fields will be deleted from the Aerospike database, we can see it by querying the price of all products — we should not get any results.

size = 0

To Summarize,

Interacting with JSON documents in Aerospike is simple, elegant and powerful using the Document API library.

Feel free to contribute code or open GitHub issues in the Aerospike Document API GitHub repository.