SQL Server 2025: Native JSON Data Type and New JSON Functions**

Introduction

For years, SQL Server developers and DBAs have been storing JSON as NVARCHAR columns. It worked, but it never felt like a real solution. SQL Server 2025 finally closes the gap with a fully native JSON data type, along with new JSON functions that behave more like what you see in PostgreSQL and MySQL’s JSON ecosystem.

This post breaks down how the JSON type works, where it shines, its limitations, how to index it properly, and how to use the new JSON functions introduced in SQL Server 2025.

---

What Is the Native JSON Data Type

With the 2025 release, SQL Server stores JSON as a binary-optimized structured column, not a string. This gives you:

• Automatic validation
• Faster reads
• Dedicated JSON indexes
• Direct JSON function support
• Native comparison and containment operations

This brings SQL Server’s JSON capability closer to a document store, while keeping relational structure as the foundation.

Here is a basic example:

CREATE TABLE PlayerProfiles2025 (
    PlayerId INT IDENTITY PRIMARY KEY,
    Profile JSON
);

INSERT INTO PlayerProfiles2025 (Profile)
VALUES 
('{"name":"Luke","rank":"Elite","games":["Fortnite","Apex"]}');

If the JSON is malformed, SQL Server throws:

Error: Invalid JSON text

Finally.

---

New JSON Functions in SQL Server 2025

The release introduces several new functions. The most important:

1. JSON_CONTAINS (value, path, target)

Checks whether a JSON document contains a specific value or structure.

Example:

SELECT JSON_CONTAINS(Profile, '$.games', '["Fortnite"]')
FROM PlayerProfiles2025;

2. JSON_ARRAYAGG (expression)

Aggregates a result set into a JSON array.

SELECT JSON_ARRAYAGG(name)
FROM dbo.Players;

3. JSON_OBJECTAGG (key, value)

Builds a JSON object from key/value pairs.

SELECT JSON_OBJECTAGG(PlayerId, name)
FROM dbo.Players;

These functions allow SQL Server to return API-ready objects without string manipulation, which becomes even more powerful in Day 10 when we call REST and GraphQL endpoints from SQL.

---

How SQL Server Stores JSON Internally

The new JSON type is not simply NVARCHAR. Internally, SQL Server stores:

• Structural metadata
• Compressed binary segments
• Typed tokens for numeric vs text values
• A hashed map for keys

That means the engine can run index seeks inside JSON with predictable performance. This is where the JSON index (Day 3) becomes important.

---

Validating JSON with SQL Server 2025

SQL Server validates JSON automatically, but you can explicitly check validity:

SELECT ISJSON(Profile)
FROM PlayerProfiles2025;

With the JSON type, ISJSON will always return 1 unless you cast NVARCHAR into JSON manually.

SQL Server also supports direct casting:

DECLARE @payload NVARCHAR(MAX) = '{ "name": "David" }';
SELECT CAST(@payload AS JSON);

Malformed JSON fails immediately at the CAST phase.

---

Querying JSON Values

These functions behave like the classic 2016 JSON model but operate on native JSON storage:

JSON_VALUE

Extract a scalar value:

SELECT JSON_VALUE(Profile, '$.name')
FROM PlayerProfiles2025;

JSON_QUERY

Extract an object or array:

SELECT JSON_QUERY(Profile, '$.games')
FROM PlayerProfiles2025;

Performing Contains Filtering

This is dramatically faster in SQL 2025 when paired with a JSON index:

SELECT *
FROM PlayerProfiles2025
WHERE JSON_CONTAINS(Profile, '$.rank', '"Elite"');

The engine no longer needs to parse NVARCHAR on every row.

---

Comparing Old vs New JSON Handling

Feature	SQL 2016–2022 (NVARCHAR)	SQL 2025 JSON Type
Native storage	No	Yes
JSON index	No	Yes
Validation	Manual	Automatic
Performance	String parsing	Binary seek
Functions	Limited	Expanded
API-ready aggregation	Not optimized	Built-in

SQL 2025 drastically improves the developer experience.

---

Migration Strategy from NVARCHAR(JSON)

Most production databases store JSON in NVARCHAR(MAX).
Migrate gradually:

1. Create a new JSON column

ALTER TABLE Orders ADD Payload2025 JSON NULL;

2. Migrate and validate rows

UPDATE Orders
SET Payload2025 = CAST(Payload AS JSON);

Rows with invalid JSON will fail, which lets you target cleanup.

3. Add the JSON index

(covered in depth on Day 3)

4. Switch your application code

Once stable, retire the old column.

---

Examples: Practical Use Cases

Here are real scenarios where the JSON type makes life easier.

1. Storing player settings or configuration

INSERT INTO Settings (Config)
VALUES ('{"sensitivity":0.4,"buildMode":"turbo"}');

2. Returning API-ready objects

SELECT JSON_OBJECTAGG(PlayerId, JSON_VALUE(Profile,'$.rank'))
FROM PlayerProfiles2025;

3. Updating JSON paths

UPDATE PlayerProfiles2025
SET Profile = JSON_MODIFY(Profile, '$.rank', 'Legendary')
WHERE PlayerId = 1;

No string hacks. Clean. Fast.

---

Workshop: Build Your JSON 2025 Playground

Use this to get hands-on.

Step 1. Create a JSON Table

CREATE TABLE GameSessions2025 (
    SessionId INT IDENTITY PRIMARY KEY,
    Metadata JSON
);

Step 2. Insert some JSON

INSERT INTO GameSessions2025 (Metadata)
VALUES
('{"team":"Blue","score":52,"players":["David","Luke"]}'),
('{"team":"Red","score":47,"players":["Mason","Eli"]}');

Step 3. Query values

SELECT 
    JSON_VALUE(Metadata,'$.team') AS Team,
    JSON_VALUE(Metadata,'$.score') AS Score
FROM GameSessions2025;

Step 4. Filter using JSON_CONTAINS

SELECT *
FROM GameSessions2025
WHERE JSON_CONTAINS(Metadata, '$.players', '["Luke"]');

Step 5. Build API output

SELECT JSON_ARRAYAGG(Metadata)
FROM GameSessions2025;

Step 6. Insert invalid JSON (test behavior)

INSERT INTO GameSessions2025 (Metadata)
VALUES ('{invalid json}');

The engine will block it.

---

Final Thoughts

The native JSON data type finally gives SQL Server a first-class document capability. It enables hybrid relational and document modeling without a performance penalty and sets the stage for advanced indexing, REST integration, and API generation later in this series. JSON handling is cleaner, faster, and far more developer-friendly than the NVARCHAR-based approach we’ve been working around since 2016.

Tomorrow’s post dives into the JSON index, which ties everything together.

---

References

• Native JSON Data Type
https://learn.microsoft.com/sql/t-sql/json/json-data-type

• JSON Functions
https://learn.microsoft.com/sql/t-sql/json/json-functions

• JSON Index
https://learn.microsoft.com/sql/t-sql/json/json-index

• SQL Server 2025 What’s New
https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2025