Values and limits

How WKL Office JSON turns JSON values into VBA values and back, and where it stops. JSON (RFC 8259) knows strings, numbers, true/false, null, objects and arrays; VBA knows typed Variant values. Every crossing between the two is listed here, together with what is lost and what is refused. The member pages of the API reference refer to this page.

A default of a typed getter is converted to the getter's type with the conversion rules of VBA (VariantChangeType); a default that does not convert (for example GetLong(path, "abc")) raises jeTypeMismatch. GetValue returns its default unchanged.

A string is read as a number only when its text without surrounding spaces is a JSON number: "42" and " 4.2 " are read; "007", "+5", ".5", "12,5" and "1 000" raise jeTypeMismatch - read such codes with GetString. For GetBoolean, a number is False when it is 0 after the conversion to Decimal; this includes numbers below 1E-28 such as 1e-30 (unlike CBool), and every other number, also one beyond the Decimal range, is True.

What SetValue, Add, Insert and CreateValue accept (Value = … of a value node takes only the values without children of this table, or a JValue; a node with children, an array or a JDoc with an object or array as root raises jeInvalidOperation there, and Value = … on an object or array always raises jeInvalidOperation):

A number keeps the text it was read with; Json, Save and GetString write that text unchanged, also 0.10, 1E3 or a number with 40 digits. Only the conversion to a VBA type can round or fail:

• GetDecimal and GetCurrency convert the text directly, not through Double, so 0.1 + 0.2 stays exact. Decimal holds 28 to 29 significant digits: a number with more digits is rounded to that precision without an error, and a number whose magnitude is below 1E-28 (for example 1e-30) becomes 0 without an error; read such values with GetDouble. Currency rounds to four decimal places.
• Value returns Long or Decimal for whole numbers; a whole number beyond the Decimal range (about 7.9 × 10^28) is returned as Double, with the precision of a Double.
• A number beyond the Double range (1e400) is valid JSON; reading it with Value or GetDouble raises jeTypeMismatch; GetString returns its text.

JSON has no date type; dates travel as strings.

Reading (GetDate, DateValue) accepts the forms that web services send (RFC 3339 profile of ISO 8601): 2026-09-25, 2026-09-25T14:30, 2026-09-25T14:30:15, a fraction of a second with a point or a comma (up to seven digits are read, more are ignored), each time optionally followed by Z, +02:00, +0200 or +02. A space instead of T is accepted. Refused: week and ordinal dates, durations, second 60, 24:00, and a date without time that carries a zone. A date before the year 100, the first date VBA knows, raises jeTypeMismatch. DateZone of the document decides what a timestamp with zone becomes: converted to local time (default), to UTC, or as written (see Dates and time zones). Local time uses the time-zone rules Windows has for the date.

Writing gives yyyy-MM-dd for a date at midnight, otherwise yyyy-MM-ddTHH:mm:ss, with .fff when the value has milliseconds, always without zone. A VBA time without date (#14:30#) is written with the VBA base date, 1899-12-30T14:30:00.

JPath implements the syntax and semantics of RFC 9535 (JSONPath). Where it differs:

• Extensions. A path without $ starts at the current node (also @ at the top level); - is allowed in short names ($.first-name); whitespace around the whole expression is ignored; a regular expression stops after one second and then yields false.
• Accepted although RFC 9535 requires an error. Function expressions that are not well-typed ($[?length(@.*) < 3], $[?length(@.a)], $[?match(@.a,'x') == true]), ! directly before a comparison, \' inside a double-quoted string, the indices -0 and 01, and indices beyond ±(2^53−1).
• Different results. Strings are ordered by UTF-16 code units, not by Unicode scalar values (this differs only between characters U+E000–U+FFFF and characters above U+FFFF). Numbers are compared as Decimal: numbers below 1E-28 equal 0, and numbers that differ only after the 28th significant digit are equal. A function of type ValueType used as a test is false for the value false.
• Regular expressions. match and search use .NET regular expressions instead of I-Regexp (RFC 9485): . matches one UTF-16 code unit (an emoji needs two) and also CR; \d, \w, \s, back-references and look-around are accepted. An invalid pattern yields false, as RFC 9535 section 2.4.6 requires. From 2.1.0 a pattern without back-references and look-around runs on an engine whose time grows only linearly with the text; a pattern with these constructs runs as before, with the one-second time limit.
• Nested expressions. From 2.1.0 an expression with more than 256 levels of parentheses, negations, function arguments and filters within filters (a filter within a filter counts twice) raises jePathSyntax.
• Paths. Path of a node returns this library's expression in dot notation where a name allows it, otherwise ['name'] ($.lines[2].no, $['first name']), not the bracket form of a Normalized Path (RFC 9535, section 2.7).

Compiled expressions are kept in a cache of 512 entries.

The objects are COM objects of the apartment model: create and use them in one thread, as VBA does; they are not thread-safe for other hosts.

Supported configuration: Microsoft 365 Access 16.0, 32-bit and 64-bit (ACCDB, ACCDE and the Access Runtime) on Windows 11. Other Access versions (2016, 2019, 2021, LTSC) and other Windows versions are not supported in this release.

• Overview
• Read exact numbers
• Dates and time zones
• Handle invalid JSON
• Query with JPath