Dynamics 365 Business Central: why not start supporting the JSON data type for table fields?

In the last months I worked on some cloud-based applications that use Azure SQL as backend (details not relevant here) and that use JSON documents extensively. Doing that I’m increasingly inclined to revive an old idea that I had already shared here in the past: why not supporting the JSON data type for table fields also in Dynamics 365 Business Central?

Azure SQL Database supports JSON data type natively from more than 1 year. The JSON type allows you to store JSON documents in a native binary format that is optimized for storage and query performance. Advantages of this new data type are a lot:

• Optimized for easy querying and manipulation
• More efficient reads, as the document is already parsed
• More efficient writes, as the query can update individual values without accessing the entire document
• More efficient storage, optimized for compression

The json type internally stores data using UTF-8 encoding, Latin1_General_100_BIN2_UTF8 and this behavior matches the JSON specification.

Azure SQL also supports a set of interesting aggregates and operators to natively work with JSON documents in a very performant way.

Just to provide some examples, here is how you can create a table with a field of type JSON:

CREATE TABLE dbo.Orders (
    order_id int NOT NULL IDENTITY,
    order_document JSON NOT NULL
);

and here is a T-SQL code that inserts a JSON document into the relative Json field:

INSERT INTO dbo.Orders (order_document)
VALUES ('
{
    "OrderNumber": "S0240001",
    "Date":"2024-11-04T08:01:00",
    "AccountNumber":"ACC001",
    "Price":159.99,
    "Quantity":1
}'), ('
{
    "OrderNumber": "S0240002",
    "Date":"2024-11-04T12:20:00",
    "AccountNumber":"ACC002",
    "Price":224.99,
    "Quantity":3
}');

Why not having the possibility to do the same in AL? Something like in the following table’s definition:

When you have a field with JSON as data type on SQL, you can easily and quickly retrieve JSON properties using SQL/JSON path expressions. For example, this code retrieves the AccountNumber property of the stored JSON document:

SELECT o.order_id, JSON_VALUE(o.order_document, '$.AccountNumber') AS account_number
  FROM dbo.Orders as o;

Can you imagine how useful could be this in AL when you have a JSON document stored in a table’s field?

In T-SQL the OPENJSON sentence also permits you to handle JSON documents and perform queries on them. Here is an example:

DECLARE @conferencecenter NVARCHAR(MAX)
SET @conferencecenter = 
N'{
 "Name": "Vienna Congress Center",
 "Location": "Vienna",
 "Floors": 3,
 "Halls": ["1A","1B","2A","3B"],
 "Offices": ["2B","3A"]
}'
---- Gets conference center details
SELECT *
FROM OPENJSON(@conferencecenter )
WITH (
 DCName VARCHAR(25) '$.Name'
 , Location VARCHAR(25) '$.Location'
 , Floors TINYINT '$.Floors'
)
---- Gets halls per conference center
SELECT Name, Hall
FROM OPENJSON(@conferencecenter)
WITH (
 Name VARCHAR(25) '$.Name'
 , Halls NVARCHAR(MAX) '$.Halls' AS JSON
) AS cc
CROSS APPLY OPENJSON(cc.Halls) WITH (
 Hall VARCHAR(10) '$'
) AS hall

Other useful Azure SQL features that I love a lot when working with JSON documents are the JSON_OBJECTAGG and JSON_ARRAYAGG aggregates functions, that permits you to construct a JSON object from an aggregation of SQL data or columns.

Here is an example. Imagine to have the following two SQL tables:

CREATE TABLE Accounts (
	AccountNumber varchar(10) NOT NULL PRIMARY KEY,
	Phone1 varchar(20) NULL,
	Phone2 varchar(20) NULL,
	Phone3 varchar(20) NULL
);
CREATE TABLE Orders (
	OrderNumber varchar(10) NOT NULL PRIMARY KEY,
	OrderTime datetime2 NOT NULL,
	AccountNumber varchar(10) NOT NULL,
	Price decimal(10, 2) NOT NULL,
	Quantity int NOT NULL
);

and on this table you have the following values:

INSERT INTO Accounts (AccountNumber, Phone1, Phone2, Phone3)
VALUES('AW29825', '(123)456-7890', '(123)567-8901', NULL),
	('AW73565', '(234)0987-654', NULL, NULL);
INSERT INTO Orders (OrderNumber, OrderTime, AccountNumber, Price, Quantity)
VALUES ('S043659',
        '2022-05-24T08:01:00',
        'AW29825',
        59.99,
        1),
	   ('S043661',
        '2022-05-20T12:20:00',
        'AW73565',
        24.99,
        3);

Now imagine that to interact with an external application (API) you need to have a single JSON document taking data from the two tables. In T-SQL you can do something like the following:

SELECT JSON_OBJECTAGG(OrderNumber:JSON_OBJECT('Date':o.OrderTime, 'Price':o.Price, 'Quantity':o.Quantity,
                                                'AccountDetails':JSON_OBJECT('AccountNumber':o.AccountNumber,
                                                    'PhoneNumbers':JSON_ARRAY(a.Phone1, a.Phone2, a.Phone3)))) AS Orders
  FROM Orders AS o
  JOIN Accounts AS a
    ON a.AccountNumber = o.AccountNumber;

and the result of this query will be a JSON document like the following:

{
    "S043659": {
        "Date": "2022-05-24T08:01:00",
        "Price": 59.99,
        "Quantity": 1,
        "AccountDetails": {
            "AccountNumber": "AW29825",
            "PhoneNumbers": [
                "(123)456-7890",
                "(123)567-8901"
            ]
        }
    },
    "S043661": {
        "Date": "2022-05-20T12:20:00",
        "Price": 24.99,
        "Quantity": 3,
        "AccountDetails": {
            "AccountNumber": "AW73565",
            "PhoneNumbers": [
                "(234)0987-654"
            ]
        }
    }
}

All is native in the platform (very performant!). I can see a lot of interesting usage on this data type and on these aggregators, so imagine to have native AL methods for doing all the operations I’ve described here directly at the SQL level.

Maybe someone can start thinking on that for vNextNext? In the meantime, there’s an idea opened here if you want to support:

Original Post https://demiliani.com/2024/11/04/dynamics-365-business-central-why-not-start-supporting-the-json-data-type-for-table-fields/