json

JSON_ARRAYAGG() in SQL Server 2025: Aggregate Rows Into JSON Arrays

by

JSON_ARRAYAGG() is one of the new features introduced in SQL Server 2025. This is an aggregation function that allows you to combine multiple row values into a single JSON array directly within your SQL queries.

JSON_ARRAYAGG() simplifies the process of generating structured JSON output from relational data, which makes it easier to build APIs, export data, and integrate with modern applications that rely on JSON. Instead of manually constructing JSON with string operations or complex subqueries, JSON_ARRAYAGG() provides a clean, efficient way to transform sets of rows into well-formed JSON arrays as part of standard SQL aggregation.

Read more

Fix “JSON aggregates do not support order-by within group when specified with grouping sets, cubes and rollups” in SQL Server

by

If you’re getting an error that reads “JSON aggregates do not support order-by within group when specified with grouping sets, cubes and rollups. Try your query without order-by within group.” it looks like you’re trying to use the ORDER BY clause inside a JSON aggregate function when using GROUPING SETS, CUBE, or ROLLUP.

To fix this error you’ll need to remove ORDER BY from the aggregate function when using those clauses.

Read more

JSON_OBJECTAGG() in SQL Server 2025: Build JSON Objects Straight from Your Queries

by

SQL Server 2025 ships with a handful of genuinely useful additions, and JSON_OBJECTAGG() is one of the better ones. It lets you aggregate rows into a single JSON object (with key-value pairs pulled directly from your data) without any string hacking or FOR JSON PATH gymnastics.

Here’s what it does, how it works, and when you might actually use it.

Read more

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