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.8

A Boolean value should be either true or false. In the below example false is the Boolean type value.

"isOnPrem": false

JsonValue

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": null

Useful 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!!!


#MSDyn365 #MSDyn365BC #BusinessCentral #DynamicsNAV

© 2020 by msnJournals.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon
  • GitHub-Mark-120px-plus