What keys are actually inside this JSON column? Schema discovery reads the documents and reports the keys that are actually present. Start with a bounded valid sample, then inspect types, nested objects, and the paths your application requires.
Establish the Document Set Before Schema Discovery
OPENJSON turns a JSON object into rows describing its properties. Without a WITH clause, those rows include key, value, and a type code. That flexible shape is useful when you do not yet know the property's name or type. It needs database compatibility level 130 or higher.
I validate the input before expanding it. Invalid text can stop the query, so guard the OPENJSON argument itself rather than assuming a separate WHERE predicate executes first. The optimizer controls evaluation order. A CASE expression supplying a valid empty object for invalid input makes the expansion safe.
Use a representative bounded sample when the table is large. Choose its ordering and selection rule deliberately. TOP by identifier is useful for a demonstration but does not promise to represent every historical document. JSON is flexible. That flexibility includes the ability to surprise the person who last documented it.
CREATE TABLE #Documents(DocumentID int PRIMARY KEY,Payload nvarchar(max));
INSERT #Documents VALUES
(1,N'{"id":1,"name":"Alpha","address":{"city":"Austin"}}'),
(2,N'{"id":2,"name":null,"address":{"zip":"10001"}}'),
(3,N'{"name":"Gamma","active":true}'),(4,N'not JSON');
SELECT DocumentID,ISJSON(Payload) AS IsValidJSON FROM #Documents;Keep schema discovery limited to the document shapes and paths needed for the current decision.
Count Documents Containing Each Key and Type
The following expansion materializes one row per top-level property in each sampled valid document. OPENJSON type codes distinguish NULL, string, number, Boolean, array, and object values. The code mapping is zero through five in that order. Keep the original code in the profile.
COUNT(DISTINCT DocumentID) reports how many documents contain the key and type combination. COUNT(*) would count property occurrences, which differs when duplicate keys appear in a document. Duplicate JSON properties deserve their own review instead of silently assuming every key appears once.
I retain the document identifier through every expansion. It makes a surprising key or type traceable to its source. If the same key appears as both a number and a string, inspect those documents before defining a typed OPENJSON WITH projection. A conversion failure during loading is easier to understand when the profile already showed the mixed types.
SELECT d.DocumentID,j.[key] COLLATE Latin1_General_BIN2 AS KeyName,
j.[value] AS KeyValue,j.[type] AS TypeCode
INTO #TopLevelKeys
FROM(SELECT TOP(100) DocumentID,Payload FROM #Documents ORDER BY DocumentID) AS d
CROSS APPLY OPENJSON(CASE WHEN ISJSON(d.Payload)=1 THEN d.Payload ELSE N'{}' END) AS j;
SELECT KeyName,TypeCode,COUNT(DISTINCT DocumentID) AS DocumentsWithKey
FROM #TopLevelKeys
GROUP BY KeyName,TypeCode
ORDER BY KeyName,TypeCode;Inspect One Nested Level Without Treating Text as JSON
An object-valued property has type code five, while an array has type code four. The next query expands the address object one level deeper. Its guarded argument ensures that nonobject values do not become accidental input to the nested parser.
Keep parent and child key names together. A child called city under address means something different from city under another object. A schema profile that reports only child names loses that context. Arrays need a separate interpretation because their keys are element positions rather than property names.
Which nested paths does the application actually consume? Follow those paths first instead of recursively expanding every possible level. Full recursive profiling can produce a large result and expose more content than the review requires. A targeted second level is easier to validate, and it supplies concrete evidence for the next schema decision. Keep values out of aggregate reports when names and types are sufficient.
SELECT k.KeyName AS ParentKey,j.[key] COLLATE Latin1_General_BIN2 AS ChildKey,j.[type] AS TypeCode,
COUNT(DISTINCT k.DocumentID) AS DocumentsWithChild
FROM #TopLevelKeys AS k
CROSS APPLY OPENJSON(CASE WHEN k.TypeCode=5 THEN k.KeyValue ELSE N'{}' END) AS j
WHERE k.KeyName=N'address'
GROUP BY k.KeyName,j.[key] COLLATE Latin1_General_BIN2,j.[type]
ORDER BY ChildKey,TypeCode;
Distinguish a Missing Path From a NULL Value
SQL Server 2022 adds JSON_PATH_EXISTS. It tests whether a path exists rather than whether a scalar extraction returns a non-NULL value. That matters when an explicitly present property contains JSON null. A missing required property and a present-but-null property can violate different business rules.
The following query finds documents lacking the top-level id path. The guarded JSON input also makes invalid documents produce the empty-object result for this check. Keep invalid rows in a separate list rather than describing them only as missing id. Their primary problem is that the document is not valid JSON.
After checking existence, validate the property's type and permitted value. A key named id containing an array satisfies path existence but does not satisfy an integer identifier contract. The profile and the business validation work together. Do not confuse a successful path test with proof that the document is ready for a typed load.
SELECT DocumentID,Payload
FROM #Documents
WHERE JSON_PATH_EXISTS(CASE WHEN ISJSON(Payload)=1 THEN Payload ELSE N'{}' END,'$.id')=0;
SELECT DocumentID FROM #Documents WHERE ISJSON(Payload)<>1 OR Payload IS NULL;Read the Profile as Evidence of Variation
Key presence counts need a denominator. Record the number of valid sampled documents and the sample selection rule. A key appearing in some documents can be optional, newly introduced, or missing because of a faulty producer. The aggregate identifies the variation; application context explains it.
Case matters for JSON property names. Properties named ID and id are different keys. The examples apply Latin1_General_BIN2 to key names so grouping preserves that distinction. Keep the consuming paths equally precise instead of assuming that a differently capitalized property name identifies the same value.
Document changes across time also deserve attention. A current sample can miss an older schema shape still present in retained data. Compare relevant production periods before enforcing a new typed projection. Preserve malformed and incompatible rows for review instead of discarding them simply to make the profile look uniform. Flexible storage does not remove the need for a clear consuming contract.
Scale Schema Discovery Without Losing Its Meaning
Expanding every document consumes CPU and creates a potentially large intermediate result. Start with a bounded sample, then run a reviewed full scan when the decision requires complete coverage. Sampling supplies candidates and proportions within the sample, not proof that an unseen key never exists.
For frequently queried paths, consider an appropriate relational representation or indexed computed extraction after validating its contract. That optimization belongs after the keys and types are understood. A fast extraction of the wrong path remains the wrong answer.
Repeat schema discovery when producers or document versions change. Keep key names, type codes, presence counts, and required-path tests as separate evidence. The useful result is a documented set of supported document shapes and a clear list of exceptions, with the original rows still available for follow-up.
Related reading on this blog: Understanding JSON NULL Value Using STRICT Keyword and 2016: Check Value as JSON With ISJSON().
A JSON column is not a documented schema, it is a collection of documents you must inspect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
