Microsoft Fabric Updates Blog

Announcing improved JSON support in Fabric DW

• Announcements
• Data Warehouse
• Microsoft Fabric

• September 26, 2024 by Jovan Popovic 95,071 Views

We’ve improved the JSON support in Fabric Datawarehouse (Fabric DW) and have added the following features in Fabric DW:

• Fabric DW enables you to use FOR JSON query option that formats the query results as JSON text. This was one of the features that is requested on Microsoft Fabric Idea site.
• Fabric DW enables you to use all JSON scalar functions that can also be used in Azure SQL database. In addition to the existing JSON scalar functions (ISJSON, JSON_VALUE, JSON_QUERY, and JSON_MODIFY), we have added support for the latest JSON functions that exist in Azure SQL Database – JSON_PATH_EXISTS, JSON_OBJECT, and JSON_ARRAY.

The JSON_PATH_EXIST function checks if there is a value on the given path in JSON text. The JSON_OBJECT and JSON_ARRAY functions enable you to more easily create JSON objects or arrays based on a set of scalar values without a need to concatenate and escape the values to produce a properly formatted JSON text.

In the following example you can see the new approach that you can use to create JSON objects in the queries:

SELECT
     CONCAT('{"name:"', name, '",
              "size":"', size,'",
              "color":"', color,'"}')
FROM Product

SELECT
     JSON_OBJECT('name':name,
                 'size': size,
                 'color': color)
FROM Product

With the new approach, you don’t need to worry about pairing curly brackets and quotes to properly format JSON objects, and you don’t need to ensure that you are properly escaping the values before you concatenate them.

In addition to the new functionalities, we have enabled you to use the OPENROWSET function with CROSS/OUTER APPLY operator. This was one of the feature requests asked in Microsoft Fabric Idea site.

With these improvements, the JSON support in Azure SQL database and Fabric DW is aligned. You can easily migrate code between Azure SQL database and Fabric without the need to rewrite and modify your T-SQL logic.