Process JSON in AL like a Pro!

Did you know that XPath from XML has a distant cousin in the JSON world called JPath? Well, it’s actually supported by Business Central. Check out how to use it in this video:

https://youtu.be/vtdPpRS_Ztk

In this video, Erik shares a handy holiday-themed tip for working with JSON in AL: how to use JPath (the JSON equivalent of XPath for XML) to quickly drill into a JSON structure and extract exactly the data you need, using Business Central’s built-in SelectToken function.

Working with JSON in AL: The Usual Approach

If you work with Business Central, you’re dealing with JSON all the time — from web services, API integrations, and various data structures. The typical approach Erik demonstrates in his videos is to process JSON structurally — navigating through objects and arrays step by step — because that’s the fastest way to extract data.

But sometimes you have a large JSON structure and you only need one specific value buried deep inside it. For those cases, there’s a much more elegant approach.

Introducing JPath and SelectToken

If you’ve worked with XML, you’re probably familiar with XPath — a query language for selecting nodes from an XML document. JSON has its own equivalent called JPath (JSON Path), and Business Central has built-in support for it through the SelectToken function.

SelectToken is a function available on any JsonToken. You pass it a JPath expression as text along with a var JsonToken, and it returns true if it finds a match. It’s a remarkably powerful way to reach deep into a JSON structure with a single expression.

Understanding JSON Structure Basics

Before diving into JPath, Erik offers a quick refresher on JSON structure:

  • Curly braces { } denote a JSON object
  • Square brackets [ ] denote a JSON array
  • Unlike XML, the root element in JSON doesn’t have a name — it’s just an object or array. This sometimes confuses people coming from an XML background, but that’s simply how JSON works.

The Microsoft Documentation Example

Erik highlights that Microsoft’s documentation for SelectToken actually includes a really good example. Consider this JSON structure:

{
  "company": {
    "employees": [
      { "id": "John", "salary": 7 },
      { "id": "Jane", "salary": 9 }
    ]
  }
}

Here we have an object with a child called company, which contains employees — an array of objects, each with an id and a salary field.

Writing a JPath Query

The JPath expression to find John’s salary looks like this:

$.company.employees[?(@.id=='John')].salary

Let’s break this down:

  • $ — represents the root of the JSON structure (remember, the root doesn’t have a name in JSON)
  • .company — navigate to the company child object
  • .employees — navigate to the employees child (which is an array)
  • [?(@.id=='John')] — apply a filter: the ? means “apply a filter,” and @.id=='John' means “where the id property equals ‘John'”
  • .salary — return the salary value from the matching element

The AL Code

Here’s how you use this in AL. The function takes a JSON token (used when the JSON could be anything — object, array, or value), an employee ID, and returns the salary:

procedure SelectEmployeeSalary(CompanyData: JsonToken; EmployeeId: Text): Decimal
var
    SalaryToken: JsonToken;
    Query: Text;
begin
    Query := StrSubstNo(
        '$.company.employees[?(@.id==''%1'')].salary',
        EmployeeId);
    CompanyData.SelectToken(Query, SalaryToken);
    exit(SalaryToken.AsValue().AsDecimal());
end;

And calling this function is straightforward:

var
    JsonObj: JsonObject;
begin
    JsonObj.ReadFrom('{"company":{"employees":[{"id":"John","salary":7},{"id":"Jane","salary":9}]}}');
    Message('%1', SelectEmployeeSalary(JsonObj.AsToken(), 'John'));
    // Displays: 7
end;

Note that when you have a JsonObject but the function expects a JsonToken, you convert it using AsToken(). The JsonToken type is the most flexible — it can represent an object, array, or value — making it ideal for generic utility functions.

When to Use a JsonToken vs. JsonObject

Erik explains the distinction clearly:

  • Use JsonObject when you know the structure is specifically an object
  • Use JsonToken when the structure could be anything — object, array, or value. It’s the generic base type.

SelectToken is available on both JsonToken and JsonObject, so you can use it in either context.

Performance Considerations

Erik provides an important caveat: SelectToken is not a performance function. Each call processes the entire JSON structure to find your result. If you need to extract many values from a large JSON structure, you’re better off navigating structurally (object by object, array by array) rather than calling SelectToken repeatedly. However, for grabbing a single value from a complex structure, it’s incredibly convenient.

Testing JPath Expressions: jsonpath.com

To help you build and test JPath expressions before putting them in your AL code, Erik recommends jsonpath.com. This interactive tool lets you:

  • Paste in your JSON structure
  • Write JPath expressions and see results instantly
  • Experiment with array indexing (e.g., [0] for the first element, [:2] for the first two elements)
  • Test filters using the ?(@.property == 'value') syntax
  • Access complete documentation on JPath syntax

The site also includes examples demonstrating various JPath features like wildcards, recursive descent, array slicing, and more.

Bonus: The JsonTools Codeunit

The source code for this project also includes a handy utility codeunit for converting between AL records and JSON. Here’s the full JsonTools codeunit:

codeunit 74500 "Json Tools"
{
    procedure Json2Rec(JO: JsonObject; Rec: Variant): Variant
    var
        Ref: RecordRef;
    begin
        Ref.GetTable(Rec);
        exit(Json2Rec(JO, Ref.Number()));
    end;

    procedure Json2Rec(JO: JsonObject; TableNo: Integer): Variant
    var
        Ref: RecordRef;
        FR: FieldRef;
        FieldHash: Dictionary of [Text, Integer];
        i: Integer;
        JsonKey: Text;
        T: JsonToken;
        JsonKeyValue: JsonValue;
        RecVar: Variant;
    begin
        Ref.OPEN(TableNo);
        for i := 1 to Ref.FieldCount() do begin
            FR := Ref.FieldIndex(i);
            FieldHash.Add(GetJsonFieldName(FR), FR.Number);
        end;
        Ref.Init();
        foreach JsonKey in JO.Keys() do begin
            if JO.Get(JsonKey, T) then begin
                if T.IsValue() then begin
                    JsonKeyValue := T.AsValue();
                    FR := Ref.Field(FieldHash.Get(JsonKey));
                    AssignValueToFieldRef(FR, JsonKeyValue);
                end;
            end;
        end;
        RecVar := Ref;
        exit(RecVar);
    end;

    procedure Rec2Json(Rec: Variant): JsonObject
    var
        Ref: RecordRef;
        Out: JsonObject;
        FRef: FieldRef;
        i: Integer;
    begin
        if not Rec.IsRecord then
            error('Parameter Rec is not a record');
        Ref.GetTable(Rec);
        for i := 1 to Ref.FieldCount() do begin
            FRef := Ref.FieldIndex(i);
            case FRef.Class of
                FRef.Class::Normal:
                    Out.Add(GetJsonFieldName(FRef), FieldRef2JsonValue(FRef));
                FRef.Class::FlowField:
                    begin
                        FRef.CalcField();
                        Out.Add(GetJsonFieldName(FRef), FieldRef2JsonValue(FRef));
                    end;
            end;
        end;
        exit(Out);
    end;

    local procedure FieldRef2JsonValue(FRef: FieldRef): JsonValue
    var
        V: JsonValue;
        D: Date;
        DT: DateTime;
        T: Time;
    begin
        case FRef.Type() of
            FieldType::Date:
                begin
                    D := FRef.Value;
                    V.SetValue(D);
                end;
            FieldType::Time:
                begin
                    T := FRef.Value;
                    V.SetValue(T);
                end;
            FieldType::DateTime:
                begin
                    DT := FRef.Value;
                    V.SetValue(DT);
                end;
            else
                V.SetValue(Format(FRef.Value, 0, 9));
        end;
        exit(v);
    end;

    local procedure GetJsonFieldName(FRef: FieldRef): Text
    var
        Name: Text;
        i: Integer;
    begin
        Name := FRef.Name();
        for i := 1 to Strlen(Name) do begin
            if Name[i] < '0' then
                Name[i] := '_';
        end;
        exit(Name.Replace('__', '_').TrimEnd('_').TrimStart('_'));
    end;

    local procedure AssignValueToFieldRef(var FR: FieldRef; JsonKeyValue: JsonValue)
    begin
        case FR.Type() of
            FieldType::Code,
            FieldType::Text:
                FR.Value := JsonKeyValue.AsText();
            FieldType::Integer:
                FR.Value := JsonKeyValue.AsInteger();
            FieldType::Date:
                FR.Value := JsonKeyValue.AsDate();
            else
                error('%1 is not a supported field type', FR.Type());
        end;
    end;
}

This codeunit provides two key capabilities:

  • Rec2Json — converts any AL record to a JSON object, automatically handling field names (replacing special characters with underscores) and different field types including dates, times, and FlowFields
  • Json2Rec — converts a JSON object back into an AL record by building a dictionary that maps JSON key names to field numbers, then assigning values through FieldRef

Here's how it's used in the page extension:

pageextension 74500 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        Json: Codeunit "Json Tools";
        JsonObj: JsonObject;
        C: Record Customer;
    begin
        JsonObj.ReadFrom('{ "No": "99999", "Name": "Yotube Demo","Invoice_Copies": "99999"}');
        c := Json.Json2Rec(JsonObj, c);
        message('%1', format(c));
    end;
}

Summary

JPath with SelectToken is a powerful tool in your AL toolbox for working with JSON in Business Central. Here are the key takeaways:

  1. SelectToken lets you use JPath expressions to drill directly into any JSON structure and extract specific values
  2. The $ symbol represents the root of your JSON structure
  3. Use dot notation to navigate child elements and [?(@.property == 'value')] syntax for filtering arrays
  4. Use jsonpath.com to test your JPath expressions interactively before writing AL code
  5. Don't overuse it — for extracting many values from a large structure, structural navigation is more performant. SelectToken shines when you need just one or two values from deep within a complex structure.
Picture of hougaard

hougaard