Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix JSON_VALUE Returning Null for a Valid Path

- 20.11.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

Covering Indexes Reduce Lookups

Covering Indexes Reduce Lookups

SQL: Use Primary Keys for Data Uniqueness

SQL: Use Primary Keys for Data Uniqueness

SQL Temporal Tables: Track Data Changes Over Time

SQL Temporal Tables: Track Data Changes Over Time

Views: 6

Post navigation

Photoshop: Fix the Magic Wand Selecting Across Hidden Layers
Git: Fix Worktrees Sharing a Stale Index Lock

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com