PostgreSQL

PostgreSQL JSON Column Query Returning Null Unexpectedly

When a query against a JSON or JSONB column returns null even though the data clearly contains the key you're looking for, it's almost always because of the specific operator being used, a key name mismatch (including case), or a misunderstanding of how PostgreSQL distinguishes a missing key from a key explicitly set to JSON null.

The Problem

You query a specific field inside a JSONB column, expecting a value, but get null instead:

SELECT data->'email' FROM users WHERE id = 1;
-- returns NULL, even though the JSON clearly has an "email" key

Manually inspecting the full JSON value confirms the key and its value are genuinely present in the stored data.

Why It Happens

A handful of specific, easy-to-miss details commonly cause this:

  • Using the wrong operator for what you actually want — -> returns the value as JSON (or JSONB), while ->> returns it as text; mixing these up in comparisons or in code expecting a specific type can produce unexpected null-like behavior depending on context
  • Case mismatch in the key name — JSON keys are case-sensitive, so querying data->'Email' against a key actually stored as "email" returns null, since these are treated as entirely different keys
  • Confusing a missing key with a key explicitly set to JSON's own null value — both return SQL NULL when queried, but they represent different underlying data, and treating them as equivalent can hide bugs in how data was inserted
  • Querying nested JSON incorrectly — using a single arrow operator when the value is actually nested one level deeper, so the query retrieves an entire nested object instead of the specific field inside it

The Fix

First, confirm the exact key name and casing by inspecting the raw JSON directly:

SELECT data FROM users WHERE id = 1;

Compare the key name character-for-character against what your query uses — a common and easy-to-miss mistake is a plural/singular mismatch or unexpected capitalization introduced by whatever originally generated the JSON.

Use the correct operator for what you actually need. For extracting a text value directly (most common in WHERE clauses or string comparisons), use the double-arrow text extraction operator:

SELECT data->>'email' FROM users WHERE id = 1;

Use the single-arrow operator only when you specifically want the result as JSON/JSONB (for example, to continue chaining further JSON operations on it, or to pass it into a function expecting JSON input):

SELECT data->'address'->>'city' FROM users WHERE id = 1;

Notice the mixed usage here: the first -> navigates into the nested address object, keeping it as JSON, and the final ->> extracts the specific city value as text. Using ->> too early in a nested path returns text that can't be navigated further, which is a common source of confusing null results when working with nested structures.

To distinguish a genuinely missing key from a key explicitly set to JSON null, check for the key's presence directly using the ? existence operator:

SELECT data ? 'email' FROM users WHERE id = 1;

This returns a boolean indicating whether the key exists at all, regardless of its value — useful for distinguishing "this field was never set" from "this field was explicitly set to null" when that distinction matters for your application logic.

Still Not Working?

If you're querying a JSON column (not JSONB) and encountering unexpected behavior specifically around whitespace or key ordering in comparisons, be aware that plain json preserves the original text representation exactly as inserted, including whitespace and key order, while jsonb normalizes and reformats the data on storage. If your application compares JSON values as text rather than using proper JSON operators, this difference can cause equality checks to fail unexpectedly even when the underlying data is logically identical — converting the column to jsonb (if not already) and using JSON-aware operators rather than text equality resolves this category of issue.

You might also like