🗄️ Fix JSON_VALUE Returning Null for a Path That Actually Exists
JSON_VALUE only ever returns a scalar, and it returns NULL with no error both when a path genuinely doesn’t exist in the stored JSON and when the path exists but resolves to an object or array instead of a scalar – and separately, when the path string has a case-sensitivity or bracket-notation mismatch against the actual stored key casing. Because all of these distinct situations produce the exact same silent NULL, a query can look like it’s simply missing data when the real cause is a type mismatch or a typo in the path expression.
🗄️ The Problem
-- Column stores: {"customer": {"address": {"city": "Izmir"}}}
SELECT JSON_VALUE(Payload, '$.customer.address')
FROM dbo.Orders;
-- Returns NULL with no error at all - not because the path is wrong,
-- but because $.customer.address resolves to an OBJECT, and
-- JSON_VALUE can only ever return a scalar.
✅ Fix: Tell Apart Missing Data From the Wrong Shape
- Before assuming the data is missing, run ISJSON() on the column and JSON_QUERY() – not JSON_VALUE – on the same path to see whether it actually resolves to an object or array, since JSON_QUERY succeeds in exactly the case where JSON_VALUE silently returns NULL for a non-scalar.
- Double-check the path’s exact key casing and bracket-versus-dot notation directly against the stored JSON, since SQL Server’s JSON path matching is case-sensitive for keys even though ordinary T-SQL identifiers elsewhere usually aren’t.
- Wrap suspect JSON_VALUE calls in a comparison against JSON_QUERY during development, so a path that resolves to the wrong shape surfaces as an obviously different result instead of blending into legitimately missing data.
🚨 Why This Is Dangerous
- A report that quietly treats “key exists but is an object” the same as “key doesn’t exist” can under-count or zero out a metric for every row where that nested value happens to be structured, not missing, with the discrepancy only showing up later during reconciliation against the source system.
JSON_VALUE’s NULL never tells you why – missing key and wrong shape look exactly the same from the outside.
