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;
NaamaTsafrir
on:
Domain specific Tags
on:
Auto-Spacing Option Between Visuals When Aligning ...
on:
In "Manage relationships" view I need to filter by...