A column full of documents should not accept broken JSON as ordinary text. The native JSON data type in SQL Server 2025 validates document structure and stores it in a dedicated binary format.
Give Flexible Content a Home in the JSON Data Type
The type lets you store a JSON object or array directly in a column. It removes the need to treat every document as unchecked nvarchar(max). SQL Server understands the stored representation. You still need rules for required properties, business values, and the relationship between the document and other columns.
I start by deciding which parts of the record belong in stable columns. Identity, ownership, and frequently filtered business fields deserve clear types and constraints. Flexible attributes can belong in a document when their structure genuinely varies. Putting everything into one payload trades schema clarity for application responsibility.
The examples use a temporary table on SQL Server 2025. Keep its relational identity separate from its flexible attributes. The sample inputs are hand-written documents, not imported customer records. Run the blocks in one session so the later reads and updates use the same table.
CREATE TABLE #CatalogItems
(
ItemID int NOT NULL PRIMARY KEY,
ItemName nvarchar(80) NOT NULL,
Attributes json NOT NULL
);
INSERT #CatalogItems VALUES
(1, N'Desk Lamp', '{"color":"blue","watts":12,"tags":["desk","indoor"]}'),
(2, N'Wall Light', '{"color":"white","watts":8,"mount":{"type":"wall"}}');
SELECT ItemID, ItemName,
CAST(Attributes AS nvarchar(max)) AS DocumentText
FROM #CatalogItems;Test How the JSON Data Type Rejects Broken Input
Invalid document syntax cannot be stored in the native column. Catch the error in a deliberate negative test and inspect its message. This checks the storage boundary instead of relying on an application's earlier validation. The database should retain its contract even when a different client writes to it.
BEGIN TRY
INSERT #CatalogItems VALUES (3, N'Broken Input', '{"color":}');
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT ItemID FROM #CatalogItems WHERE ItemID = 3;Valid syntax is a narrow promise. A document with an unexpected property name or a negative wattage can still be valid JSON. SQL NULL in a nullable column also differs from JSON's null token inside a document. Define those meanings before deciding that successful insertion proves useful data.
Extract Scalars With the Existing Functions
JSON_VALUE returns a scalar property. JSON_QUERY returns a JSON object or array fragment. Choose the function that matches the expected shape. Trying to extract an array through a scalar function hides the structure behind a NULL or error, depending on the path mode.
SELECT ItemID,
JSON_VALUE(Attributes, '$.color') AS ColorValue,
TRY_CONVERT(int, JSON_VALUE(Attributes, '$.watts')) AS WattsValue,
JSON_QUERY(Attributes, '$.tags') AS TagArray,
JSON_QUERY(Attributes, '$.mount') AS MountObject
FROM #CatalogItems
ORDER BY ItemID;The conversion makes wattage a typed SQL value for later calculations. TRY_CONVERT returns NULL for an invalid integer representation. Distinguish that failure from a legitimately missing property in validation. A query that quietly drops both cases into the same category obscures the reason an input needs correction.
Change a Property Without Rebuilding the Contract
JSON_MODIFY returns a changed document expression. Assign that expression back to the column in an UPDATE. The WHERE clause identifies the intended row. Native storage does not excuse an unbounded update. Review the target identities just as you would for an ordinary relational change.
UPDATE #CatalogItems
SET Attributes = JSON_MODIFY(Attributes, '$.color', N'green')
WHERE ItemID = 1;
UPDATE #CatalogItems
SET Attributes = JSON_MODIFY(Attributes, '$.watts', 15)
WHERE ItemID = 1;
SELECT ItemID, JSON_VALUE(Attributes, '$.color') AS ColorValue,
JSON_VALUE(Attributes, '$.watts') AS WattsValue
FROM #CatalogItems
WHERE ItemID = 1;Pass a number when the property should be numeric. Passing quoted text establishes a string instead. When inserting an object or array fragment, use JSON_QUERY so it is treated as structured JSON rather than an escaped string. Test parsed types alongside displayed values, since both can look similar in a report.
Compare the JSON Data Type With a Checked Text Column
A text column with an ISJSON check constraint provides structural validation on older designs. That remains a valid storage contract when native support is unavailable. The constraint must reject invalid text, and nullability must be defined separately. A check evaluating to unknown does not itself prohibit SQL NULL.
CREATE TABLE #LegacyDocuments
(
ItemID int NOT NULL PRIMARY KEY,
DocumentText nvarchar(max) NOT NULL,
CHECK (ISJSON(DocumentText) = 1)
);
INSERT #LegacyDocuments
SELECT ItemID, CAST(Attributes AS nvarchar(max))
FROM #CatalogItems;
SELECT ItemID, JSON_VALUE(DocumentText, '$.color') AS ColorValue
FROM #LegacyDocuments;The JSON data type makes document validation and storage representation part of the type. The older pattern makes validity a separate constraint on text. Neither design automatically enforces every property name or business rule. Compare behavior on your accepted documents before changing an existing application column.
Keep Core Business Rules Relational
Use real columns when a field needs foreign keys, a stable required type, or routine joins. A customer identifier hidden only inside JSON is harder to protect through ordinary relational constraints. Flexible storage works best when the flexibility serves a defined need instead of avoiding an uncomfortable schema decision.
API payload retention is one useful case. Preserve the received document for traceability, then expose accepted business fields through a controlled import. Variable product attributes provide another case. Keep the stable product identity and common searchable fields relational. Give changing attributes a documented property contract.
I review which properties appear in WHERE and JOIN clauses before accepting the design. Frequently searched properties need an indexing strategy. Native storage does not guarantee every document predicate gets a seek. Measure the actual plans for your query shapes before promising a performance improvement from the type change alone.
Validate Paths and Missing Properties
Property names and paths are case-sensitive in JSON matching. A document using Color does not necessarily satisfy a query looking for color. Default lax paths return NULL for missing scalar properties. Strict paths raise errors for missing values. Choose the behavior that matches the import or query contract.
SELECT ItemID, JSON_VALUE(Attributes, 'lax $.missing') AS OptionalValue
FROM #CatalogItems;
BEGIN TRY
SELECT JSON_VALUE(Attributes, 'strict $.missing') AS RequiredValue
FROM #CatalogItems WHERE ItemID = 1;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ValidationError;
END CATCH;Which missing property should reject the record instead of remaining optional? Write that answer into tests for every document version. Include wrong types, empty arrays, null properties, and repeated keys. Structural validity does not decide how your application interprets ambiguous or incomplete documents.
Adopt the JSON data type when a genuine document belongs beside relational data. Retain typed columns for the rules they express well. Review storage, driver round trips, query plans, and document contracts together. Test the actual application driver with large documents and non-ASCII characters. Confirm that parameters retain the document type or accepted text conversion without truncation. Include retrieval after modification, since a client can accept an insert while displaying the returned document differently. Keep those round-trip tests alongside the database validation tests. That gives the flexible part of the design a clear boundary and a maintainable purpose.
Related reading on this blog: Storing JSON in SQL Server and Validating JSON Parameters Before a Procedure Uses Them.
A valid JSON document is not a complete business schema, it is structured content that still needs a contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
