JSON Columns vs Flat Columns

Concepts covered: JSON Extraction, Basic Types, Safe Casting, Null Handling, Storage Optimization

This one usually arrives as a follow-up once your extraction runs: 'That payload column is flexible. Why don't we store every table like that?' The interviewer is checking whether you know what a column does for you that a key inside a document cannot. The answer that scores names the specific losses, each with a concrete example from the table in front of you.

amount as a NUMERIC column
  • The type is enforced on write: inserting 'N/A' fails
  • NOT NULL and CHECK (amount >= 0) are enforced by the database
  • A misspelled name fails before the query reads a row
  • Read as stored, already a number
  • Listed in the catalog with its name and type
amount as a key inside payload
  • Any value is accepted: 49.90, "49.90", "N/A" and true all insert
  • No constraint applies unless someone writes one per key
  • A misspelled path runs and returns NULL
  • Extracted and cast in every query that uses it
  • Known only to whoever reads sample documents

The typo that compiles

A misspelled column fails fast. A misspelled path does not fail at all: the engine cannot know which keys a document holds until it reads it, so asking for a key that never appears is legal and returns NULL on every row. SUM over all NULLs is NULL, COUNT of the extracted value is 0, and a dashboard tile goes blank with no error in any log.

-- Column typo: stops before reading a row
SELECT SUM(ammount) AS revenue FROM orders;
-- ERROR:  column "ammount" does not exist

-- Path typo: runs over every purchase, returns NULL
SELECT SUM((payload->>'ammount')::NUMERIC) AS revenue
FROM events
WHERE payload->>'event_type' = 'purchase';
-- revenue: NULL

This is why the CTE habit matters beyond readability. A typo on one CTE line shows up as a column of NULLs the moment you select from it; the same typo buried in a WHERE clause shows up as a smaller number nobody questions.

There is no catalog entry for a key either. information_schema.columns lists amount when it is a column; when it is a key, the only way to learn that it exists, how it is spelled and what values it holds is to read documents. With a 10-row sample in an interview, read the sample aloud before you write a path.

Bad values surface when you read

A typed column rejects a bad value at insert, so the producer finds out. A JSON key stores whatever arrives, and the problem appears later, in whichever query casts it. If one client sent "amount": "N/A", then (payload->>'amount')::NUMERIC fails the entire query in Postgres with invalid input syntax for type numeric, and the failing row might be one purchase in a full day of events. Snowflake and BigQuery have safe casts that return NULL for that row instead and let the query finish. In Snowflake the VARIANT has to become a string first, because TRY_CAST accepts only string input: write TRY_CAST(payload:amount::STRING AS NUMBER(10,2)) or TRY_TO_NUMBER(payload:amount::STRING, 10, 2). Applied straight to payload:amount, TRY_CAST raises an error instead of returning NULL. BigQuery's SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) already starts from text, since JSON_VALUE returns a string.

Name both behaviours and their cost. A failed query is loud: someone looks at it today. A NULL is quiet: the revenue total drops by one order and nobody notices. A strong answer pairs the safe cast with a count of the rows that came back NULL, so the quiet failures get a number attached.

-- BigQuery: keep the query alive, and measure what the safe cast lost
SELECT
  COUNT(*) AS purchases,
  COUNTIF(SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) IS NULL) AS missing_or_bad_amounts,
  SUM(SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC)) AS revenue
FROM events
WHERE JSON_VALUE(payload, '$.event_type') = 'purchase';

Every read pays the extraction

A column is read as stored. A key is found inside its document on every query that touches it, and in a row store such as Postgres that means reading the whole jsonb value, even when it carries 30 keys the query ignores. On 4 sample rows the cost is invisible. On an events table taking 40 million rows a day, a query whose only filter is an extracted key has to open payload on every row of the table before it can tell which rows qualify. The fix within one query is to always include a predicate on a typed column such as received_at. With received_at indexed or used as the partition key, the engine skips every row outside the range before it reads a single payload, so extraction runs over a day of rows instead of the whole table.

The planner picks that plan because the received_at predicate exists, wherever it sits in the WHERE clause: writing the range first or last produces the same plan. Some interviewers test this directly by asking whether the date condition should move to the top. The answer that scores says position does not matter. What limits the scan is a selective predicate on a typed column that is indexed or partitioned, and EXPLAIN shows the index scan or partition pruning that proves it.

About this lesson

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.