Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

jovanpop-msft
Microsoft Employee
‎10-07-2025 11:00 AM

Query and ingest JSONL files in Data Warehouse and SQL Analytics Endpoint for Lakehouse (Generally Available)

The OPENROWSET function that can read JSONL format empowers you to easily read and ingest JSONL files - for example log files, social media streams, machine learning datasets, configuration files, and other semi-structured sources. With the versatile OPENROWSET T-SQL function, you can reference and query JSONL files as if they were tables, eliminating the need for manual parsing or complex transformation steps.

Querying JSONL data

The OPENROWSET function allows you to directly read one or multiple JSONL files, representing each JSON object as a row in your query results.

SELECT *
FROM OPENROWSET(BULK
    '/Files/samples/jsonl/farmers-protest-tweets-2021-2-4.jsonl'
)

Each property within your JSON objects is automatically mapped to a separate column, simplifying data exploration and manipulation.

Additionally, the WITH clause in OPENROWSET function enables you to define a schema and extract properties at any level—even from nested or complex JSON structures—flattening content on the fly for easier analysis.

You can use either Fabric Query editor, T-SQL Notebook, or tools like SSMS to query JSONL files. In the following picture you can see a Fabric T-SQL Notebook where we are reading the nested JSON documents representing social media data stored in JSONL files:

Query_and_ingest_JSONL_files_in_Data_Warehouse_and_SQL_Analytics_Endpoint_for_LaQuery_and_ingest_JSONL_files_in_Data_Warehouse_and_SQL_Analytics_Endpoint_for_La

Querying JSONL file with the nested sub-properties

These capabilities make it straightforward to work with deeply nested JSON data and unlock insights from diverse, semi-structured sources.

Flexible Data Ingestion Workflows

The OPENROWSET function is a powerful tool that also enables various data ingestion scenarios. You can use CREATE TABLE AS SELECT (CTAS) and INSERT SELECT statements to efficiently load JSONL data into your Fabric Data Warehouse tables.

Query_and_ingest_JSONL_files_in_Data_Warehouse_and_SQL_Analytics_Endpoint_for_LaQuery_and_ingest_JSONL_files_in_Data_Warehouse_and_SQL_Analytics_Endpoint_for_La

Loading JSONL file content into a new table

This query loads content of JSONL file into a new Data Warehouse table where you can proceed with further analysis and transformations.

Once you create the table, you can inser additional data using INSERT-SELECT statement:

INSERT INTO OpenRowsetDW.dbo.Tweets
SELECT * FROM OPENROWSET(BULK '/Files/jsonl/farmers-protest-2022-12-04.jsonl')

This streamlines the process of populating and refreshing datasets from JSONL sources, supporting both initial loads and ongoing pipeline automation.

Conclusion

JSONL support through OPENROWSET, Fabric Data Warehouse and SQL Analytics Endpoint for Lakehouse deliver a powerful tool for reading and ingesting JSON files at scale. Whether you are working with logs, configuration files, or streaming data, this integration simplifies workflows and accelerates analytics—helping you unlock the full potential of your semi-structured data.

0