json

JSON Indexes in SQL Server 2025

by

Before SQL Server 2025, indexing JSON data meant creating a computed column to extract the value you cared about, then indexing that column. It works, and it’s still a solid approach, but it requires you to know upfront which paths you’ll query, maintain separate columns for each one, and keep those columns in sync with any schema changes. SQL Server 2025 introduces JSON indexes as a native alternative. It’s a single index structure that covers the JSON column directly, without the computed column overhead.

JSON indexes are currently in preview and only available in SQL Server 2025 on-premises. They aren’t available yet on Azure SQL Database, Managed Instance, or Fabric. That said, things could be different by the time you read this article.

Read more

Fix Error 13683 “Invalid JSON paths in JSON index” Due to Overlapping Paths in SQL Server

by

If you’re getting SQL Server error 13683 stating “Invalid JSON paths in JSON index“, it sounds like you could be specifying overlapping paths in your JSON index definition.

A JSON index cannot contain overlapping paths.

To fix this, remove the overlap from the specified paths that make up the index definition.

Read more

Understanding JSON_CONTAINS() in SQL Server 2025

by

JSON_CONTAINS() is a new function introduced in SQL Server 2025 that lets you check whether a value exists at a specific path in a JSON document. It returns an integer. This is 1 if the value is found, 0 if not, and NULL if any argument is null or the path doesn’t exist. That makes it a natural fit for WHERE clauses when you’re filtering rows based on JSON content.

It’s currently in preview (although this will almost certainly change soon) and only available from SQL Server 2025.

Read more

JSON Data Type in SQL Server: What It Is and How to Actually Use It

by

SQL Server added native JSON support back in SQL Server 2016, and as of SQL Server 2025, it officially introduced a dedicated JSON data type. If you’re on an older version, you’ve probably been storing JSON in NVARCHAR(MAX) columns and parsing it with functions like JSON_VALUE() and OPENJSON(). That still works, but the new JSON type gives you validation, better storage, and cleaner semantics.

And along with the JSON type, we get some new JSON related features. Let’s take a quick walk through.

Read more

Pivoting JSON Data in SQL Server

by

JSON has become a common format for data exchange, and SQL Server’s built-in JSON support makes it straightforward to work with JSON data directly in your queries. But what happens when you need to pivot JSON data – transforming nested structures or array elements into a columnar format for reporting or analysis?

SQL Server provides functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY() that let you extract and manipulate JSON data. Combined with standard pivoting techniques, you can reshape JSON data into whatever format you need. This can be particularly useful when you’re receiving JSON from APIs, storing semi-structured data, or working with configuration data that doesn’t fit neatly into traditional tables.

Read more

How to Convert JSON to Rows and Columns in SQL Server

by

Modern applications often exchange information in JSON, and that data often ends up in SQL Server. While JSON’s flexible structure makes it ideal for storing dynamic or nested data, it doesn’t fit neatly into traditional relational tables. The good news is that SQL Server includes a good selection of JSON functions that let you parse, query, and transform JSON content into structured rows and columns. This means that you can work with your JSON data just like any other table.

Read more

Using JSON_MODIFY() to Update Nested JSON in SQL Server

by

Working with JSON data in SQL Server has become quite common as more applications are relying heavily on semi-structured data. When your JSON includes nested objects or arrays, it’s useful to know how to make precise updates without rewriting the entire document. Fortunately, SQL Server makes this relatively simple with the JSON_MODIFY() function. This function lets you update specific values inside JSON text stored in a table, even deep within the structure.

In this article, we’ll walk through an example of how to use SQL Server’s JSON_MODIFY() function to update nested JSON.

Read more

How to Export SQL Server Query Results as JSON with FOR JSON AUTO

by

SQL Server makes it surprisingly simple to generate JSON directly from a query. If you’ve ever had to send data to a web service, feed an API, or just save query results in a structured text format, JSON is a natural choice. Instead of writing complex formatting logic in your application, you can let SQL Server do the heavy lifting with the FOR JSON clause. And you can use the AUTO keyword to have the JSON formatted automatically by SQL Server.

In this article, we’ll look at how FOR JSON AUTO works, along with some examples to demonstrate its usage.

Read more

7 Ways to Extract Data from JSON in DuckDB

by

Most DuckDB distributions come with the JSON extension, and this extension is loaded upon first use. That means we can go ahead and run queries against JSON data right out of the box. One common task we’ll face when working with JSON is extracting data from within the JSON documents. This can include extracting scalar values, or extracting nested JSON from within the outer document.

DuckDB provides us with multiple ways to extract such data. The option we use will largely depend on our use case. Either way, here are seven options for extracting data from JSON documents in DuckDB.

Read more

OPENJSON() vs JSON_VALUE() When Parsing JSON in SQL Server

by

Working with JSON in SQL Server often comes down to choosing the right function for the job. Two of the most common options are OPENJSON() and JSON_VALUE(). Both are designed to pull data out of JSON documents, but they work in very different ways and are suited to different scenarios. Knowing when to use each one can save time and simplify your queries.

This article breaks down how OPENJSON() and JSON_VALUE() handle JSON parsing, what makes them different, and the situations where one might be a better fit than the other. Whether you are cleaning up nested JSON or just extracting a single value, understanding these functions will help you work more effectively with JSON data in SQL Server.

Read more