Retain the evidence
Consider two fictional imported messages: one contains orderId A10 and amount 120; the other contains orderId A11 and amount "unknown". Both may be syntactically valid JSON, but the second is not a usable numeric amount for a revenue fact table. Keep the original payload with a source identifier and arrival time so the rejected row can be investigated without guessing what the sender supplied.
Check syntax before extraction
ISJSON can help distinguish JSON syntax from arbitrary text in SQL Server. That check does not establish that required business fields exist or have the right meaning. Validate required identifiers separately and handle conversion failures deliberately. Do not coerce an unknown amount to zero and publish it as a genuine zero-value order. A quarantine table should preserve the reason, the source record key and the rule version.
Extract to typed columns
For a scalar property, JSON_VALUE can expose the value for further validation. Use an appropriate conversion rather than adding strings together. This example illustrates SQL Server functions and should be checked against your engine version and the actual payload contract. An array of order lines requires a row-expansion approach such as OPENJSON rather than pretending the entire array is one scalar.
SELECT source_id,
JSON_VALUE(payload, '$.orderId') AS order_id,
TRY_CONVERT(decimal(18,2), JSON_VALUE(payload, '$.amount')) AS amount
FROM dbo.RawOrders
WHERE ISJSON(payload) = 1;Make missing and invalid different
A NULL extraction can have several causes, including a missing property or an unsuitable value for the extraction expression. Record the specific validation outcome before loading the fact table. In the example, A10 should become a typed amount of 120.00. A11 should be held for review, not silently dropped from an otherwise “successful” batch. Compare accepted plus rejected counts with the raw batch count.
Design for contract changes
When a sender introduces a nested currency object or renames a field, retain the payload version and update the extraction mapping intentionally. Monitor unexpected property absence and conversion failures. Indexing choices depend on how the typed or computed fields are queried, not simply on the presence of JSON. Use a representative execution plan and reconcile results before claiming that a JSON-related optimization made reporting faster.
