The source sends JSON, and storing the whole document feels convenient. Storing JSON in SQL Server works best when validation, query paths, and stable columns are planned together.
Decide Why the Document Stays When Storing JSON
Keep JSON when the source payload matters for replay, audit, or fields that change frequently. Extract stable business keys, dates, and amounts into typed columns when reports filter or join on them. A document and relational columns can coexist. The choice is not all or nothing.
I ask whether the database needs to query a field or merely retain it. If a customer ID appears in every WHERE clause, hiding it inside JSON adds parsing and complicates constraints. A rarely used optional attribute can stay in the document.
Give the stored payload a source ID, arrival time, and schema version when those exist. They make a bad row traceable. The JSON text alone does not tell you which delivery produced it.
CREATE TABLE dbo.ApiReceipt
(
ReceiptId bigint IDENTITY(1,1) PRIMARY KEY,
SourceId nvarchar(100) NOT NULL,
ArrivedAtUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
Payload nvarchar(max) NOT NULL
);Validate JSON Before Storing It in nvarchar
SQL Server has long supported JSON functions over nvarchar text. ISJSON checks that a value is syntactically valid JSON. A CHECK constraint can prevent malformed documents from entering a text column. Syntax validity is only the first gate. Required properties and types need separate checks.
I test a missing key, a wrong type, and a duplicate property in sample payloads. A document can be valid JSON and still violate the application’s contract. Parse the fields used by business logic into typed values before publishing them.
For an existing table, check current rows before adding the constraint. The deployment can fail if invalid documents already exist. Keep those rows for review rather than deleting them to make the ALTER succeed.
ALTER TABLE dbo.ApiReceipt
ADD CONSTRAINT CK_ApiReceipt_Payload_IsJson
CHECK (ISJSON(Payload) = 1);Use the Native json Type in 2025
SQL Server 2025 includes a native json type that stores a parsed binary representation. It can improve operations on documents, but migration still needs testing for drivers, functions, storage, and application expectations. Do not replace nvarchar columns solely because a new type exists.
I create a small test table and compare the source payload, extracted fields, and client behavior before moving production data. The native type validates JSON shape as part of storage. It does not validate that CustomerId is present or that Amount is a valid business amount.
Keep the compatibility baseline explicit. If the same script runs on older SQL Server instances, a native json column cannot be used there. Use a version-specific deployment plan rather than a hidden fallback.
CREATE TABLE dbo.JsonInbox
(
InboxId bigint IDENTITY(1,1) PRIMARY KEY,
Payload json NOT NULL
);
Extract Values With Explicit Types
JSON_VALUE returns a scalar path value. OPENJSON with a WITH clause can extract an array into typed rows. Use a path that reflects the source contract, then check missing and invalid values. A NULL result can mean missing data or a path mismatch.
I keep source IDs in a relational column even when they also appear in the payload. That allows a unique constraint and a direct join. The document remains available for less stable fields and investigation.
Be careful with numeric precision. A decimal extracted from text should use a type that fits the domain. A successful JSON parse does not guarantee the resulting number can fit a target decimal column.
DECLARE @payload nvarchar(max) =
N'{"items":[{"id":1,"amount":12.50}]}';
SELECT SourceId, Amount
FROM OPENJSON(@payload, '$.items')
WITH (SourceId int '$.id',
Amount decimal(18,2) '$.amount');Index Frequently Queried Paths
For nvarchar JSON, a computed column exposing JSON_VALUE can support a conventional index when its expression and type are designed correctly. Cast to a bounded type so an index key has a sensible size. Test the report’s predicate to confirm the optimizer uses the computed value.
SQL Server 2025 also has JSON index capabilities for native json columns, with their own requirements and limitations. Check current feature status and table prerequisites before choosing that route. A clustered primary key is required for JSON index creation.
I compare query reads and write cost. An index that helps one frequent filter can be worthwhile. Indexing every property of a flexible document turns flexibility into maintenance work. Let actual access patterns choose the paths.
ALTER TABLE dbo.ApiReceipt
ADD CustomerCode AS
CONVERT(nvarchar(100), JSON_VALUE(Payload, '$.customerCode'));
CREATE INDEX IX_ApiReceipt_CustomerCode
ON dbo.ApiReceipt(CustomerCode);Keep Documents and Columns in Sync
If a business field exists in both JSON and a relational column, define which is authoritative. Populate both in one controlled load, and validate they agree. A later update to only one copy can make reports and raw evidence disagree.
I prefer immutable raw receipts plus curated columns in a separate accepted table for important pipelines. That keeps source evidence intact and lets business corrections have their own audit. For simpler systems, one table can work if write rules are clear.
Do not use JSON as a way around schema ownership. A stable field deserves a named type and constraint even if the source sends it inside a document. The document can keep flexibility where the domain actually needs it.
Measure and Secure the Workload for Storing JSON
JSON parsing and indexing consume CPU and storage. Test common filters, updates, and ingestion under representative volume. A small document query can look effortless while a broad report repeatedly parses many rows. Precompute stable fields when that pattern appears.
Apply normal access controls to payloads. A document can contain fields not shown in curated columns, including sensitive values. A read permission on the payload is broader than a read permission on a single business attribute. Keep logging and retention aligned with that reality.
Storing JSON is useful when the document is treated as a source contract with known paths and validation. Keep the document where it helps, expose stable facts as columns, and measure the actual query shapes before selecting an index strategy.
JSON gives a flexible boundary for events and external payloads, but it does not excuse an undefined schema. Document required properties, types, allowed values, and version changes. Which fields must be queried frequently? Those deserve deliberate indexing or a relational projection. I keep large, rarely queried payloads away from hot row paths when their size hurts the workload.
Validation needs more than ISJSON when a property is required. Test missing fields, explicit JSON null, duplicate property names, and arrays where an object was expected. Decide whether the caller rejects the payload or stores it for later inspection. If a report depends on one field, establish how that field is extracted and typed in one place. SQL Server can store JSON, but the application still owns its contract.
Related reading on this blog: SQL SERVER Performance: JSON vs XML and 2016: Check Value as JSON With ISJSON().
JSON storage is not a substitute for a data model, it is a way to retain flexible source shape beside one.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
