Foundation · Libraries · Uncategorized

Query JSON documents with JSONPath

We have extended our JSON and BSON library with functionality to query JSON documents using an XPath-like query language called JSONPath.

About JSONPath

JSONPath is to JSON what XPath is to XML. It enables you to search for data in a JSON document using a simple query language somewhat similar to XPath. Although there is no single official JSONPath standard, one of the most popular ones is developed by Stefan Gössner. Our implementation is based on his specification.

A example JSONPath declaration looks like this:

$.store.book[0].title

or

$['store']['book'][0]['title']

Both representations are identical: you can use either dot (.) or bracket ([]) notation to denote children of a dictionary. Brackets can also be used with numerical indices to denote children of an array by index.

This sample query searches for the title of the first book in a store. We will show you an example JSON document to which you can apply this query a bit later.

NOTE: JSONPath only uses single quotes (') within brackets. We also allow for double quotes (") since these are easier to use in Delphi strings.

Syntax

The JSONPath syntax is not very complicated. We show a couple of examples later to clarify the syntax.

  • Every path starts with a $ indicating the root, followed by zero or more child operators (. or []). A $ by itself matches the entire document.
  • A child name can be an identifier string or the asterisk (* or '*') wildcard to match all children. For example: $.store.book[*].author matches the authors of all books in the store.
  • In addition to a single dot (.), a double dot (..) can be used to search for any descendants instead of immediate children. For example, $..author matches all authors, regardless of depth. This is called recursive descent.
  • Children can also be accessed by one or more indices between brackets. These indices are 0-based and are only used with arrays. You can separate multiple indices with comma’s. For example, $.store.book[0,2,3] matches the first, third and fourth books.
  • You can use the slice notation [Start:End:Step] to match a slice (range) of children. This matches all children from index Start up to (but not including) End, using a given Step size (usually 1). All are optional, but at least one value (and colon) must be given:
    • If Start is omitted, it is implied to be 0. A negative value indicates an offset from the end of the array.
    • If End is omitted, the slice extracts through the end of the array. A negative value indicates and offset from the end of the array.
    • If Step is omitted, is is implied to be 1.
    • Examples:
      • List[2:] matches the third and all following elements.
      • List[-2:] matches the last two elements.
      • List[:2] matches the first two elements.
      • List[:-2] matches all but the last two elements.
      • List[2:-2] matches all elements but the first two and last two.
      • List[-4:-2] matches the 3rd and 4th elements from the end.
      • List[::2] matches all elements with an even index.

NOTE: JSONPath also has an @ operator to allow custom (java)script expressions. We do not support this operator.

Examples

To clarify the syntax, we show an example JSON document and some example queries. These are taken directly from Stefan’s website (with some modifications).

Example Document

{ "store": {
    "book": [
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}

Example Queries

Path Result
$ Matches the root document (a single value)
$..* Matches all members in the document (lots of values)
$.store.book[*].author The authors of all books in the store
$..author All authors
$.store.* All things in store (2 books and a bicycle)
$.store..price The price of everything in the store
$..book[2] The third book
$..book[-1:] The last book in order
$..book[:2] The first two books

JSONPath in Delphi

Our JSONPath implementation can be found in the unit Grijjy.Bson.Path in our GrijjyFoundation repository on GitHub. The API is short and simple. It consists of a TgoBsonPath record with only a couple of methods.

For one-off matching, use the static Match method:

var
  Doc: TgoBsonValue;
  Matches: TArray<TgoBsonValue>;
begin
  Doc := TgoBsonValue.LoadFromJsonFile(...);
  Matches := TgoBsonPath.Match(Doc, '$.store.book[*].author');
end;

If you plan to use the same path on multiple (sub)documents, then it is faster to parse the path once, and then apply it multiple times:

var
  Doc1, Doc2: TgoBsonValue;
  Path: TgoBsonPath;
  Matches1, Matches2: TArray<TgoBsonValue>;
begin
  Doc1 := TgoBsonValue.Parse(...);
  Doc2 := TgoBsonValue.Parse(...);

  Path := TgoBsonPath.Create('$.store.book[*].author');

  Matches1 := Path.Match(Doc1);
  Matches2 := Path.Match(Doc2);
end;

You can also run the path on sub-trees:

var
  Doc: TgoBsonDocument;
  Store: TgoBsonValue;
  Matches: TArray<TgoBsonValue>;
begin
  Doc := TgoBsonDocument.LoadFromJsonFile(...);
  Store := Doc['store'];
  Matches := TgoBsonPath.Match(Store, '$.book[*].author');
end;

If you are only interested in a single (or the first) match, then you can use MatchSingle instead:

var
  Doc, Match: TgoBsonValue;
begin
  Doc := TgoBsonValue.Parse(...);
  if (TgoBsonPath.MatchSingle(Store, '$.book[*]', Match)) then
    ...
end;

More examples can be found in the unit tests (in Tests.Grijjy.Bson.Path.pas).

We hope you will find this small addition useful for extracting information from your JSON documents.

One thought on “Query JSON documents with JSONPath

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s