Published: 2026-04-28

SQL Server JSON Support: From OPENJSON to the Native JSON Type

For nearly a decade, SQL Server treated JSON as "NVARCHAR(MAX) with helper functions". Azure SQL Database introduced a native JSON type, and SQL Server 2025 brings it on-premises (currently in preview). Here's how to use both eras — and how they compare to MongoDB. As of May 2026, SQL Server 2025 (17.x) is in preview with native JSON, and Azure SQL Database / Managed Instance have it GA.

Quick Answers

Q: Does SQL Server have a JSON data type?

A: Yes — the native json type is GA in Azure SQL Database and Azure SQL Managed Instance, and in preview on on-prem SQL Server 2025 (17.x). SQL Server 2016–2022 store JSON in NVARCHAR(MAX) and query it with JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON. The native type stores documents in an optimised binary format with significantly faster path access.

Q: What is the difference between JSON_VALUE and JSON_QUERY?

A: JSON_VALUE returns a scalar at a path (use in WHERE); JSON_QUERY returns a JSON fragment (object or array, when you need a sub-document).

Q: How do I index JSON in SQL Server?

A: Add a persisted computed column on the JSON path you query, then index that column. Azure SQL Database and SQL Server 2025 also expose dedicated JSON index features on top of the native json type, but the computed-column pattern works on every version from 2016 forward.

Q: Can SQL Server replace MongoDB?

A: For most enterprise document workloads, yes — native JSON (Azure SQL / 2025), JSON_PATH_EXISTS, OPENJSON shredding into rowsets, and full ACID across documents and relational tables. MongoDB is still preferred for shard-native write scaling and aggregation-pipeline-heavy data engineering teams.

A Short History of JSON in SQL Server

• SQL Server 2016 — JSON_VALUE, JSON_QUERY, OPENJSON, ISJSON, FOR JSON, and JSON_MODIFY for in-place updates. JSON was stored as NVARCHAR(MAX); the engine just understood how to parse it.
• SQL Server 2022 — JSON_PATH_EXISTS, JSON_OBJECT, JSON_ARRAY, and the ISJSON(..., type) overload. Storage still NVARCHAR(MAX).
• Azure SQL Database / Managed Instance — native binary JSON data type (GA), JSON_OBJECTAGG, JSON_ARRAYAGG.
• SQL Server 2025 (17.x) — the native JSON data type comes on-prem (in preview at the time of writing), along with JSON_OBJECTAGG / JSON_ARRAYAGG and broader ANSI SQL/JSON conformance.

If you maintain on-prem SQL Server 2022 or earlier, you'll be working with the NVARCHAR(MAX)-plus-ISJSON pattern. If you're greenfield on Azure SQL or SQL Server 2025, prefer the native JSON type. See the official JSON in SQL Server reference for the complete function catalogue.

JSON Storage Choices Today

SQL Server gives you three places to put a JSON document. (See the native json data type reference for the up-to-date availability matrix.)

1. Native JSON type (Azure SQL DB GA; SQL Server 2025 preview) — binary storage, fast path access, validates on insert.
2. NVARCHAR(MAX) with a CHECK constraint — works back to 2016 and is still the only option on on-prem SQL Server 2022 and earlier; no validation by default, so add CHECK (ISJSON(data) = 1) to enforce.
3. VARCHAR(MAX) — smaller for ASCII-only payloads, but loses Unicode safety. Avoid unless you have a specific reason.

Querying JSON: The Function Cheatsheet

SQL Server has no operator shorthand — everything goes through functions. As of May 2026, the function surface below is the stable cross-version baseline (2016+), with JSON_PATH_EXISTS and friends added in 2022 and JSON_OBJECTAGG / JSON_ARRAYAGG arriving with the native type. Full syntax for OPENJSON and the JSON functions is on Microsoft Learn.

-- Scalar extraction (use this in WHERE)
SELECT * FROM customers
WHERE JSON_VALUE(data, '$.email') = '[email protected]';

-- Sub-document extraction (returns JSON)
SELECT JSON_QUERY(data, '$.address') FROM customers;

-- Path existence (2022+)
SELECT * FROM customers
WHERE JSON_PATH_EXISTS(data, '$.phone') = 1;

-- Validity check (declared-on-text columns)
SELECT * FROM customers WHERE ISJSON(data) = 1;

-- Shred a JSON array into rows
SELECT o.id, j.sku, j.qty
FROM orders o
CROSS APPLY OPENJSON(o.items)
  WITH (sku NVARCHAR(64) '$.sku', qty INT '$.qty') AS j;

-- In-place update (returns new doc)
UPDATE customers
SET data = JSON_MODIFY(data, '$.phone', '+1-555-0100')
WHERE id = 42;

Indexing JSON Paths

The standard pattern across all supported SQL Server versions: add a persisted computed column, then index that column.

ALTER TABLE customers
  ADD email AS JSON_VALUE(data, '$.email') PERSISTED;

CREATE INDEX idx_customers_email ON customers(email);

The trade-off: persisted computed columns store the extracted value alongside the row, doubling the storage of any path you index. In return you get a regular B-tree that the optimiser uses without hesitation.

The persisted-computed-column pattern works on every version from 2016 forward and remains the safer choice for cross-version code. Azure SQL Database and SQL Server 2025 also expose dedicated JSON index features on top of the native JSON type, but the computed-column approach is still documented as the universal path in the SQL Server JSON docs.

FOR JSON and OPENJSON: The Two Workhorses

These two functions are the reason SQL Server can do real document work:

• FOR JSON projects relational rows into a JSON document — the SQL Server equivalent of building a JSON response in your API layer, but in T-SQL.
• OPENJSON shreds a JSON document back into a rowset, so you can join, filter, and aggregate over array elements.

Together they let you keep the API contract relational while storing flexible documents under it.

SQL Server JSON vs MongoDB

SQL Server has historically been the document database of choice for shops already running on the Microsoft data stack. The detailed comparison:

Pick SQL Server JSON when you already run SQL Server, you need cross-document transactions, or you want the same engine to power your relational and document workloads with one set of backups and one DR plan.

Pick MongoDB when you start with horizontally-sharded write scale, your team is heavily invested in the aggregation pipeline, or you genuinely have schemaless data that never settles.

A Realistic Schema Pattern

CREATE TABLE events (
  id          BIGINT IDENTITY PRIMARY KEY,
  user_id     BIGINT      NOT NULL,
  event_type  VARCHAR(64) NOT NULL,
  occurred_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  data        JSON        NOT NULL  -- Azure SQL / SQL Server 2025+; use NVARCHAR(MAX) earlier
);

ALTER TABLE events
  ADD event_name AS JSON_VALUE(data, '$.name') PERSISTED;

CREATE INDEX idx_events_user_time
  ON events (user_id, occurred_at DESC);
CREATE INDEX idx_events_name
  ON events (event_name);

Working with SQL Server JSON in a SQL IDE

Even SSMS — the long-time SQL Server gold standard — renders JSON as a single-line escaped blob. The story is worse for the legacy NVARCHAR(MAX) pattern, where the IDE has no idea the column is JSON at all.

Jam SQL Studio recognises the native JSON type and lets you declare any long-text column as JSON in MetaInfo — the same operator dropdown then exposes JSON path filtering, with the engine-correct ISJSON() guard wrapped around the predicate automatically. Cells render as collapsible trees, the filter chip exposes path-aware sub-operators (has property, property =, property contains), and a Peek popover scans the loaded rows to show every JSON path in the column. The tool works the same against the native type on Azure SQL Database and SQL Server 2025, and against the NVARCHAR(MAX) declared-as-JSON pattern on every prior version back to 2016.

Last verified against Microsoft Learn (SQL Server, Azure SQL) on 2026-05-13.