Press "Enter" to skip to content

Category: JSON

Querying JSON Quickly in SQL Server 2025

Published 2025-11-13 by Kevin Feasel

Brent Ozar follows up on yesterday’s post:

SQL Server 2025 and .NET 10 bring several new improvements to storing JSON natively in the database and querying it quickly.

On the SQL Server 2025 side, the two big ones are the new native JSON indexes and the new JSON_CONTAINS function. Let’s see their improvements in action. On the .NET 10 side, EF 10 not only supports the new JSON data type, but on databases of compatibility level 170 or higher, EF will automatically migrate JSON data from NVARCHAR(MAX) data types over to JSON the next time you do a migration, as explained in the What’s New in EF Core 10 doc. That makes it especially important for you to understand how the new JSON indexes work, because they may be coming at you quickly the instant you change your compatibility level.

Read on to see Brent’s take.

Leave a Comment

Working with JSON Indexes in SQL Server 2025

Published 2025-11-05 by Kevin Feasel

Koen Verbeeck tries out a new index type:

We’re trying the new JSON data type in SQL Server for data stored as JSON in a table. When we query it using functions such as JSON_VALUE, we see a full table scan is performed for each query. Is there a way we can index the JSON to improve performance?

The JSON index has a somewhat different definition of its structure and there are some limitations to how it works, but for specific JSON-related queries, you can see the improvement.

3 Comments

JSONL Support in Fabric Data Warehouse and Lakehouse now GA

Published 2025-10-08 by Kevin Feasel

Jovan Popovic announces that JSONL format support is now generally available in Microsoft Fabric:

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.

Read on to see examples of how to ingest and use data in the JSON Lines format.

Comments closed

Using JSON Arrays instead of JSON Objects for Serialization

Published 2025-08-13 by Kevin Feasel

Lukas Eder makes a recommendation:

Why, yes of course! jOOQ is in full control of your SQL statement and knows exactly what column (and data type) is at which position, because you helped jOOQ construct not only the query object model, but also the result structure. So, a much faster index access is possible, compared to the much slower column name access.

The same is true for ordinary result sets, by the way, where jOOQ always calls JDBC’s ResultSet.getString(int), for example, over ResultSet.getString(String). Not only is it faster, but also more reliable. Think about duplicate column names, e.g. when joining two tables that both contain an ID column. While JSON is not opinionated about duplicate object keys, not all JSON parsers support this, let alone Java Map types.

Read on for some insight into when you might want to choose either of the two approaches, and why Lukas went with JSON arrays instead of JSON objects for object serialization in jOOQ.

Comments closed

JSON Lines Support in Microsoft Fabric

Published 2025-07-30 by Kevin Feasel

Jovan Popovic makes an announcement:

We’re happy to announce the preview of JSON Lines (JSONL) support in the OPENROWSET(BULK) function for Microsoft Fabric Data Warehouse and SQL 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.

Click through to see it in action.

Comments closed

JSON Indexes in SQL Server 2025 CTP 2.0

Published 2025-05-20 by Kevin Feasel

Daniel Hutmacher gives it a try:

Starting today, the public preview of SQL Server 2025 is available to download!

One really interesting new feature that got my attention was the addition of JSON indexes. I’m a big fan of everything that makes working with JSON easier, since JSON blobs are so much easier to work with than table variables when you’re moving data from point A to point B. This is especially true when you’re working with complex, relational data.

Daniel lays out some of the limitations of JSON index creation and also some of the performance gains you might see from it. This will be most helpful in data engineering scenarios, shredding JSON from various services, but the normalization purist in me says that if you’re shredding JSON enough to need indexes, it’s probably time to normalize that data.

1 Comment

Working with the JSON Data Type in Azure SQL DB

Published 2025-05-08 by Kevin Feasel

Dennes Torres tries out the JSON data type in Azure SQL Database:

Before this new field type, JSON data was typically stored in varchar(max) columns. There are many features to use with JSON values stored in varchar(max) columns and variables, but storing JSON as regular strings is still limited.

The built-in JSON type expands the possibilities. Using an actual JSON column, it becomes easier to build constraints related to JSON columns, for example.

Dennes also spends a lot of the article covering the JSON_ARRAYAGG() and JSON_OBJECTAGG() functions.

Comments closed