Add native OPENROWSET(json) support in Fabric DW

[jovanpop-msft]

Fabric Warehouse currently supports the OPENROWSET function, which allows you to read CSV files directly. However, when it comes to reading JSON files, you need to use a workaround. This involves treating JSON files as text or CSV files and then using OPENJSON and JSON functions to parse the JSON documents. While this method works, it is considered a hacky workaround and not very elegant.

To improve this, Fabric Warehouse should add a native support for JSON in the OPENROWSET function. This would eliminate the need for tricks with OPENJSON and JSON functions, making the process of reading JSON files more straightforward and elegan.

The current workaround for reading json is:

SELECT 
TRY_CAST(JSON_VALUE(jsonContent,'$.orderId') AS INT) AS orderId,
JSON_VALUE(jsonContent,'$.orderDate') AS orderDate,
JSON_QUERY(jsonContent,'$.orderDetails') AS orderDetailsObject,
JSON_QUERY(jsonContent,'$.deliveryAddress') AS deliveryAddressArray
FROM
OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/datalakehouseuk/raw/json/orders.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR ='0x0b',
    FIELDQUOTE = '0x0b'
)
WITH (
    jsonContent VARCHAR(500)
) AS r;

[albertoramonzgz]

Interesting that nobody said anything about read XML 😄

[jovanpop-msft]

@albertoramonzgz feel free to propose this as a new idea

[OR1614]

I agree, I would like to see native OPENROWSET(json) support in Fabric DW. 

[jovanpop-msft]

This feature is in public preview: JSON Lines Support in OPENROWSET for Fabric Data Warehouse and Lakehouse SQL Endpoints (Preview) | M...

We are supporitng JSONL format. Oter formats are not in the roadmap.