SQL Server has had JSON functions since 2016, and SQL Server 2025 added a native JSON data type on top of them. Most write-ups explain the syntax; fewer show what actually differs between storing JSON as text and storing it as the native type, or when reaching for JSON in a relational database is the wrong call in the first place. This post runs the real functions against real data, including a genuinely surprising result on storage size.
Parsing JSON Into Rows: OPENJSON
OPENJSON turns a JSON array into a relational result set, real rows and columns, one call:
DECLARE @j NVARCHAR(MAX) = N'[
{"order_id": 101, "status": "Shipped", "total": 42.50},
{"order_id": 102, "status": "Pending", "total": 19.99}
]';
SELECT order_id, status, total
FROM OPENJSON(@j)
WITH (
order_id INT '$.order_id',
status VARCHAR(20) '$.status',
total DECIMAL(10,2) '$.total'
);
Real output:
| order_id | status | total |
|---|---|---|
| 101 | Shipped | 42.50 |
| 102 | Pending | 19.99 |
The WITH clause is doing real work here, it defines the shape and types of the output, and the '$.order_id'-style paths map each column back to a specific key in the source JSON. Without a WITH clause, OPENJSON returns a generic key/value/type result instead, useful for exploring an unfamiliar payload, less useful for a query that needs typed columns.
Serializing Rows Back to JSON: FOR JSON
The reverse direction, a real query result turned into a JSON document:
SELECT order_id, status, total
FROM #Orders
FOR JSON PATH, ROOT('orders');
Real output, one row, one JSON text column:
{"orders":[{"order_id":101,"status":"Shipped","total":42.50},{"order_id":102,"status":"Pending","total":19.99}]}FOR JSON PATH gives you full control over the output shape (nested objects via dotted aliases, for example); FOR JSON AUTO infers the shape from the query’s table structure instead, less control, less typing. ROOT('orders') wraps the array in a named root property, without it you get a bare array.
SQL Server 2025’s Native JSON Type
Before 2025, “JSON” in SQL Server was always just text, an NVARCHAR(MAX) column that happened to hold valid JSON, with no enforcement that it stayed that way. SQL Server 2025 adds a real JSON data type. Two concrete differences, tested directly on this instance:
It Actually Validates
DECLARE @bad_json JSON = N'{not valid json}';
Real result:
Msg 13609
JSON text is not properly formatted. Unexpected character 'n' is found at position 1.The assignment itself fails. An NVARCHAR(MAX) column holding the same malformed string accepts it silently, the invalid JSON only surfaces later, the first time something actually tries to parse it with JSON_VALUE or OPENJSON, potentially much further downstream from where the bad data was written.
It Isn’t Automatically Smaller
The assumption going in was that a native, presumably binary-ish type would store more compactly than plain text. Testing it on the same small document:
| Storage | Bytes for {"a": 1, "b": [1,2,3]} |
|---|---|
NVARCHAR(MAX) |
44 |
native JSON |
76 |
The native type was larger for this small document, not smaller. That doesn’t mean the native type is worse, validation on write and the query optimizer statistics support that come with a real type are real advantages, but “it’s more storage-efficient” isn’t one of them, at least not for small documents. Whether the storage picture changes at larger, more repetitive document sizes is worth testing against your own actual payloads rather than assuming either direction.
Indexing JSON Data
- A computed column extracting a specific JSON property with
JSON_VALUE, then a regular index on that computed column, is the standard way to make a specific JSON field seekable. The index is on the computed column, not on the JSON blob itself. - Querying inside a large JSON document without a supporting computed column and index means a full scan plus JSON parsing on every row, expensive at any real table size.
- Don’t index every possible JSON property speculatively. Index the properties actual queries filter or join on, the same discipline as any other indexing decision.
When JSON Is the Wrong Call
- Data you regularly filter, join, or aggregate on belongs in real columns. JSON functions add parsing overhead to every query that touches them; a proper column with a proper index doesn’t.
- A genuinely variable, sparse, or externally-defined schema (a third-party API payload you store as-received, an audit/event log where the shape varies by event type) is the legitimate case for JSON. You’re not fighting the relational model there, you’re storing something that doesn’t have one.
- “We might need more fields later” is not a reason to reach for JSON. Adding a nullable column is cheap in modern SQL Server; a live table doesn’t need a schemaless escape hatch just in case.
Best Practices
- Use
ISJSON()to validate JSON stored in anNVARCHARcolumn (pre-2025 pattern, or when the native type isn’t available) before trusting it parses correctly. - Prefer the native
JSONtype on SQL Server 2025+ when the data is genuinely JSON-shaped; the write-time validation alone catches a real class of bug earlier. - Add a computed column and index for any JSON property a real query needs to filter or join on. Don’t leave frequently-queried fields buried in unindexed JSON.
- Test storage size against your actual document shapes rather than assuming the native type is automatically more compact, this post’s own test shows it isn’t guaranteed.
Related Scripts
- Get Missing Indexes, for finding whether a computed-column index over JSON data is actually being used
- Get Implicit Conversions, a related class of hidden query cost worth checking alongside JSON parsing overhead
Leave a Reply