Basics of JSON data types in Business Central
This post is about understanding the basics of JSON and how to handle JSON data in Business Central AL Language using JSON data types. JSON data types are introduced in AL Language with Business Central / Microsoft Dynamics NAV 2018. JSON stands for JavaScript Object Notation. The JSON format is a lightweight data interchange format originally specified by Douglas Crockford.
Why JSON?
JSON is text only, easy to read, easy to understand and lightweight format to transfer the data between applications or from the client to the server (Web APIs / Web Services). JSON files are also used to maintain configuration data for example AL project's app.json, vscode's launch.json, settings.json are the JSON configuration files.
JSON Data types
Following are the data types supported in JSON Objects. An Object or an Array can be root of the JSON file / document.
JsonObject
JsonArray
String
Number
Boolean
Null
Primitive data types supported in JSON format are String (Text, Code), Number (Integer, Decimal), and Boolean data types only. Because JavaScript supports String, Number and Boolean data types only, all other types are Object types in JavaScript. To support other complex types, they are serialized into text.
Example: the following is a Date type property, but it is formatted as String. But the value can be retrieved as Date data type by using JsonValue.AsDate() method.
"orderDate": "2022-01-21"JsonObject
JSON objects are always enclosed with curly braces, and it can contain one or more properties separated by comma.
Property / Attribute / Member
JsonObject can only contain properties, JSONProperty has key value pair syntax. Key values are separated by colon symbol (:). The property key should be a string enclosed with double quotes ("), and the property value can be of any data type supported by JSON.
In the below example "name" is the key, and "Business Central" is the value.
"name": "Business Central"JsonObject with single property
{
"name": "Business Central"
}JsonObject with multiple properties
{
"name": "Business Central",
"version": 16.1,
"company": "Microsoft",
"isOnPrem": false
}JsonArray
Array is a set of values where the value can be of any data type.
In the below example, "numbers" property value is a string array, "odds" property value is number array, and "customers" property value is a array of objects.
{
"numbers": ["One", "Two", "Three"],
"odds": [1, 3, 5, 7],
"customers": [
{ "name": "Nike" },
{ "name": "Adidas" },
{ "name": "Puma" },
]
}String, Number, Boolean
String values should be enclosed with double quotes. In the below example "Business Central" is a string type value.
"name": "Business Central"Number can be an integer value or a decimal value. In the below example 43 and 5.8 are the number type values.
"age": 43,
"height": 5.8A Boolean value should be either true or false. In the below example false is the Boolean type value.
"isOnPrem": falseJsonValue
JsonValue variable is a joker type variable, it can contain value of any primitive data type like Text, Code, Integer, Decimal, Date, Date Time Date Formula etc.
JsonToken
JsonToken variable is a joker type variable, it can contain JsonObject, JsonArray, or JsonValue variable. It is like Variant data type for JSON variables.
Null
Null values are used when there is no value. Null means nothing or unknown, null doesn’t mean false or zero or empty. In the below the example "modifiedOn" is null.
"modifiedOn": nullUseful Facts
In AL by default JsonObject, JsonArray variables are auto initialized. To assign a new instance to the same variable, "Clear" method should be used.
In AL by default JsonValue, JsonToken variables are initialized to null.
All JSON variables are reference types (pointer). This means even method parameter is not defined as a reference type (var), but still it is treated as reference type. This also means, if a JSON variable is modified anywhere in the code flow (anywhere in the call stack) the original variable is modified.
Construction of JsonObject
The following code is an example of how to create JSON Object. The JsonObject (JSalesOrder) of a Sales Order is returned after adding multiple properties to it.
local procedure SalesOrderToJson(SalesHeader: Record "Sales Header"): JsonObject
var
JSalesOrder: JsonObject;
begin
// Sales Order Properties
JSalesOrder.Add('orderNo', SalesHeader."No.");
JSalesOrder.Add('orderDate', SalesHeader."Order Date");
JSalesOrder.Add('sellToCustomerNo', SalesHeader."Sell-to Customer No.");
JSalesOrder.Add('amountIncludingVAT', SalesHeader."Amount Including VAT");
JSalesOrder.Add('isApprovedForPosting', SalesHeader.IsApprovedForPosting());
JSalesOrder.Add('lines', SalesLinesToJson(SalesHeader));
exit(JSalesOrder);
end;Construction of JsonArray
The following code is an example of how to create or add multiple items to a JsonArray. The JsonArray (JSalesLines) of multiple Sales Lines JsonObjects are returned.
local procedure SalesLinesToJson(SalesHeader: Record "Sales Header"): JsonArray
var
SalesLine: Record "Sales Line";
JSalesLines: JsonArray;
begin
SalesLine.SetRange("Document Type", SalesHeader."Document Type");
SalesLine.SetRange("Document No.", SalesHeader."No.");
if SalesLine.FindSet then
repeat
AddSalesLineToJson(SalesLine, JSalesLines);
until SalesLine.Next() = 0;
exit(JSalesLines);
end;
local procedure AddSalesLineToJson(SalesLine: Record "Sales Line"; JSalesLines: JsonArray)
var
JSalesLine: JsonObject;
begin
// Sales Line Attributes
JSalesLine.Add('type', SalesLine.Type.AsInteger());
JSalesLine.Add('no', SalesLine."No.");
JSalesLine.Add('quantity', SalesLine.Quantity);
JSalesLine.Add('unitPrice', SalesLine."Unit Price");
JSalesLine.Add('amount', SalesLine."Line Amount");
JSalesLines.Add(JSalesLine);
end;Sample Output of a Sales Order JSON Object in text format of the above code:
{
"orderNo": "101009",
"orderDate": "2022-01-21",
"sellToCustomerNo": "38128456",
"amountIncludingVAT": 2887.11,
"isApprovedForPosting": true,
"lines": [
{
"type": 2,
"no": "1976-W",
"quantity": 5.0,
"unitPrice": 396.562,
"amount": 1982.81
},
{
"type": 2,
"no": "1964-W",
"quantity": 2.0,
"unitPrice": 452.152,
"amount": 904.3
}
]
}Read JsonObject
The following code is an example of how to read properties from a JsonObject. It is reading properties of the Sales Order JsonObject (JSalesOrder).
local procedure ReadSalesOrderJson(JSalesOrder: JsonObject; SalesHeader: Record "Sales Header")
var
JOrderNoToken: JsonToken;
JOrderDateToken: JsonToken;
JSellToCustomerNoToken: JsonToken;
JLinesToken: JsonToken;
JLinesArray: JsonArray;
begin
if JSalesOrder.Get('orderNo', JOrderNoToken) then
SalesHeader."No." := JOrderNoToken.AsValue().AsCode();
if JSalesOrder.Get('orderDate', JOrderDateToken) then
SalesHeader."Order Date" := JOrderDateToken.AsValue().AsDate();
if JSalesOrder.Get('sellToCustomerNo', JSellToCustomerNoToken) then
SalesHeader."Sell-to Customer No." := JSellToCustomerNoToken.AsValue().AsCode();
if JSalesOrder.Get('lines', JLinesToken) then begin
JLinesArray := JLinesToken.AsArray(); // Array of Objects
ReadSalesLinesJson(JLinesArray, SalesHeader);
end;
end;Read JsonArray
The following code is an example on how to read Items from JsonArray. It reads Sales Lines from JsonArray (JSalesLines).
local procedure ReadSalesLinesJson(JSalesLines: JsonArray; SalesHeader: Record "Sales Header")
var
SalesLine: Record "Sales Line";
JSalesLineToken: JsonToken;
JSalesLine: JsonObject;
JTypeToken: JsonToken;
JNoToken: JsonToken;
JQuantityToken: JsonToken;
begin
foreach JSalesLineToken in JSalesLines do begin
JSalesLine := JSalesLineToken.AsObject();
SalesLine."Document Type" := SalesHeader."Document Type";
SalesLine."Document No." := SalesHeader."No.";
if JSalesLine.Get('type', JTypeToken) then
SalesLine.Type := "Sales Line Type".FromInteger(JTypeToken.AsValue().AsInteger());
if JSalesLine.Get('no', JNoToken) then
SalesLine."No." := JNoToken.AsValue().AsCode();
if JSalesLine.Get('quantity', JQuantityToken) then
SalesLine.Quantity := JQuantityToken.AsValue().AsDecimal();
end;
end;Conclusion
Though it is not a great post, you can find several posts explaining everything about JSON, but I still feel there is a need of this post so that Business Central developers can understand the basics of JSON in their language. I saw many developers using JSON data types without understanding the basics (mostly copy, and paste). In my opinion, even if one has successfully completed an integration project using JSON documents etc. without knowing the fundamentals, it has no worth.
Never late to begin, learn the basics to be a better coder.
Happy Coding!!!