WKL Office JSON
Color scheme: Light
Riviera
Light
Dark
You are here: start » en » Documentation » Microsoft Access development tools » WKL Office JSON » WKL Office JSON how-to guides » How to: Query JSON with JPath

How to: Query JSON with JPath

Find nodes and values in a document by path, filter array elements by their values, and search at any depth.

Solution overview

SelectNodes returns all matches of a JPath expression, SelectSingleNode (short form JPath) the first one or Nothing, Exists whether there is one. The getters (GetString …) read the first match as a typed value. JPath follows RFC 9535 with the extensions and deviations listed in Values and limits.

Implementation

ExpressionFinds
$The root
$.customer.name, $['first name']A member; brackets for names with spaces or special characters
$.lines[0], $.lines[-1]An element by 0-based position; negative positions count from the end
$.lines[*]All elements
$..idEvery id at any depth
$.lines[0,2], $.lines[1:3]A selection, a slice (end excluded)
$.lines[?@.price < 10 && @.kind == 'A']Elements whose members match; also [?(@.price < 10)]
$.lines[?match(@.no, 'A-.*')]Filter functions: length, count, match, search, value
customer.nameWithout $: starting at the node the method is called on 
Dim line As JNode
For Each line In doc.SelectNodes("$.lines[?@.price >= 100]")
    Debug.Print line.GetString("no"), line.GetCurrency("price")
Next
 
' A default covers both a missing key and the value null.
Debug.Print doc.GetString("$.customer.vatId", "")
 
Dim first As JNode
Set first = doc.SelectSingleNode("$..items[?@.stock == 0]")
If Not first Is Nothing Then Debug.Print first.Path

Remarks

  • match tests the whole value against a regular expression, search any part of it. The patterns are .NET regular expressions, not I-Regexp (RFC 9485); an invalid pattern yields false, as RFC 9535 prescribes, and a pattern that runs longer than one second yields false as well. See Values and limits.
  • Keys are case-sensitive, as in JSON.
  • Exists is True also for a key whose value is null; a getter without a default then raises an error. Read optional values with a default (GetString(path, "")), which covers a missing key and null alike.
  • An invalid expression raises jePathSyntax with the position of the problem.
  • Path of a node returns this library's canonical expression (dot notation, not the bracket form of an RFC 9535 Normalized Path) that finds it again, for example $.lines[2].
  • Compiled expressions are cached; using the same expression in a loop over many records costs no repeated parsing.
en/wiki/msaccess/json/how-to/query-with-jpath.txt · Last modified: by Wolfgang van der Stille