Read JSON Lines files with OPENROWSET in Fabric Data Warehouse and Lakehouse SQL Analytics Endpoint
We’re happy to announce that now you can read JSON Lines (JSONL) using the OPENROWSET(BULK) function for Microsoft Fabric Data Warehouse and SQL Analytics Endpoints for Lakehouses.
The OPENROWSET(BULK) function allows you to query external data stored in the lake using well-known T-SQL syntax. With this update, you can now also query files in JSON Lines format, expanding the range of supported formats and simplifying access to semi-structured data.
What is JSON Lines file format?
JSON Lines (JSONL) is a file format in which each line is a separate JSON object. This structure is ideal for streaming and logging scenarios, where new records are continuously appended. Here’s a simple example of a JSONL file with the updated, confirmed, recovered, and country properties:
{"updated":"2025-07-01", "confirmed":120, "recovered":100, "country":"Germany"}
{"updated":"2025-07-01", "confirmed":130, "recovered":110, "country":"France"}
{"updated":"2025-07-01", "confirmed":140, "recovered":120, "country":"Italy"}This format is commonly used in systems that process large amounts of structured data, such as logging platforms, streaming services, machine learning pipelines, and big data analytics engines. JSON Lines makes it easy to handle records line-by-line, enabling efficient storage, search, and processing across a wide range of modern data workflows. Common file extensions for JSON Lines include .jsonl, .ndjson, and .ldjson.
Querying JSONL Files with OPENROWSET
To read a JSONL file, use the familiar OPENROWSET(BULK) syntax and provide a path to your file:
If your file uses some of the standard JSONL file extensions, the format will be inferred automatically. Otherwise, explicitly specifying FORMAT = ‘JSONL’ ensures correct parsing of the underlying files.
Handling Complex JSON Structures
By default, OPENROWSET(BULK) projects top-level JSON properties from each JSON object as the output columns. Nested objects are returned as JSON text. You can customize the output using the WITH clause to define specific columns and their types:
SELECT TOP 10 *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl')
WITH (updated date,
id int,
confirmed int,
deaths int,
recovered int,
latitude float,
longitude float,
country varchar(100) '$.country_region'
);The column names defined in the WITH clause should align with the property names in the JSON document. If they differ, you can explicitly specify the property name using a JSON path following the data type. For nested properties, JSON paths can also be included directly within the column definitions. This approach eliminates the need to use T-SQL functions like JSON_VALUE or OPENJSON to extract data from JSON objects.
Summary
The JSON Lines support to OPENROWSET(BULK) marks a significant step forward in simplifying how you interact with semi-structured data in lake. Whether you’re working with streaming logs, ML datasets, or operational telemetry, this enhancement makes querying JSONL files seamless and efficient—no more parsing raw text with T-SQL functions. Along with a cleaner syntax, native support also unlocks measurable performance benefits, helping teams work smarter and faster across their data platforms.
This feature is generally available and you can learn more about the reading files with OPENROWSET(BULK) on Fabric DataWarehouse documentation page or try the sample queries using this T-SQL notebook.
