Please enter your information to subscribe to the Microsoft Fabric Blog.

Thanks for subscribing to the Microsoft Fabric Blog. You’ll receive email notifications when we publish new posts.

Microsoft Fabric Updates Blog

  1. Updates
  2. September 2024

Announcing improved JSON support in Fabric DW

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:

Formatting without JSON_OBJECTFormatting with JSON_OBJECT
SELECT
     CONCAT('{"name:"', name, '",
              "size":"', size,'",
              "color":"', color,'"}')
FROM Product
SELECT
     JSON_OBJECT('name':name,
                 'size': size,
                 'color': color)
FROM Product
Formatting the vales in the rows as JSON

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.

Related blog posts

Announcing improved JSON support in Fabric DW

Blog's featured image

Announcing public preview of VARCHAR(MAX) and VARBINARY(MAX) types in Fabric Data Warehouse
October 31, 2024 by Jovan Popovic

Fabric Data Warehouse is a modern data warehouse optimized for analytical data models, primarily focused on the smaller numeric, datetime, and string types that are suitable for analytics. For the textual data, Fabric DW supports the VARCHAR type that can store up to 8KB of text, which is suitable for most of the textual values … Continue reading “Announcing public preview of VARCHAR(MAX) and VARBINARY(MAX) types in Fabric Data Warehouse”

Read more
Blog's featured image

APIs for Managed Private Endpoint are now available
October 29, 2024 by Dandan Zhang

Managed private endpoints allow Fabric experiences to securely access data sources without exposing them to the public network or requiring complex network configurations. We announced General Availability for Managed Private Endpoint in Fabric in May of this year. Learn more here: Announcing General Availability of Fabric Private Links, Trusted Workspace Access, and Managed Private Endpoints. … Continue reading “APIs for Managed Private Endpoint are now available”

Read more