A Key Whose Type Varies by Row

Concepts covered: Type Casting, Safe Casting, Conditional Logic, JSON Extraction

This one shows up as a number that is slightly too small. "Revenue rebuilt from the items array is 3% under the finance ledger." "This join from events to users loses about a fifth of rows." The JSON parses fine; the same key is typed differently by different writers. Order 1004 came from an older Android build that serialises price as "19.99", while the iOS app sends 12.5. To a JSON parser those are a string and a number, and a function that expects one misreads, rejects or quietly skips the other.

Which function breaks depends on what it expects. Casting the JSON value directly, (item->'price')::numeric in Postgres, works on the number and fails the whole query on the string with cannot cast jsonb string to type numeric. Comparisons on the raw JSON value fail silently: Postgres sorts jsonb by type before value, with every Number above every String, so item->'price' > '15'::jsonb is false for "19.99". Equality has the same flaw, because the number 123 and the string "123" are different JSON values, so a join on payload->'user_id' never matches across the 2 writers.

Profile the types before casting

-- Postgres: what types does price actually arrive as?
SELECT
  jsonb_typeof(e->'price') AS price_type,
  COUNT(*)                 AS line_rows
FROM orders_raw AS o
CROSS JOIN LATERAL jsonb_array_elements(o.payload->'items') AS e
GROUP BY 1
ORDER BY 2 DESC;

This is the query to open with when a type question lands, before any cast is written. Every engine has the probe: jsonb_typeof in Postgres, JSON_TYPE in BigQuery, TYPEOF on a VARIANT in Snowflake. The output says which branches the conversion needs, and on a real feed the long tail is where the surprises sit: an object where a scalar was expected, a boolean, an empty string. Six rows here; across 42,000 orders a day the third type might be 14 rows, and those 14 are what the follow-up will ask about.

Normalise through text, cast defensively

Text extraction is the common ground across types. ->> in Postgres, JSON_VALUE in BigQuery, JSON_EXTRACT_SCALAR in Trino and ::STRING in Snowflake all return 12.5 for the number and 19.99 for the string, both as unquoted text. Cast that text to a decimal once, with the safe variant so a bad value becomes NULL instead of failing the run, and count those NULLs so the loss is visible.

-- Trino: one numeric price whatever the writer sent, plus a loss counter
WITH lines AS (
  SELECT
    o.order_id,
    CAST(JSON_EXTRACT_SCALAR(i.item, '$.qty') AS INTEGER) AS qty,
    JSON_EXTRACT_SCALAR(i.item, '$.price')                AS price_text
  FROM orders_raw AS o
  CROSS JOIN UNNEST(CAST(JSON_EXTRACT(o.payload, '$.items') AS ARRAY(JSON))) AS i(item)
)
SELECT
  SUM(qty * TRY_CAST(price_text AS DECIMAL(10, 2)))                 AS revenue,
  COUNT(price_text)                                                  AS prices_present,
  COUNT(price_text) - COUNT(TRY_CAST(price_text AS DECIMAL(10, 2)))  AS prices_unparsed
FROM lines;

The last column answers the follow-up before it is asked. TRY_CAST turns "N/A", "12,50" and "" into NULL, and SUM skips NULLs, so a defensive cast without a counter swaps a crash for an undercount nobody notices. Report the unparsed count beside the total, or fail the job when it crosses a threshold such as 0.1% of lines. BigQuery spells the safe cast SAFE_CAST; Snowflake uses TRY_TO_DECIMAL on the text.

When text is the wrong common ground

Routing through text works for prices. Four other cases need the type probe and an explicit branch, and naming them is what turns a correct answer into a thorough one.

-- Postgres: branch on the type when the formats differ
CASE jsonb_typeof(e->'ts')
  WHEN 'number' THEN to_timestamp((e->>'ts')::bigint)
  WHEN 'string' THEN (e->>'ts')::timestamptz
END AS event_ts

• 2^53

The full answer the interviewer is grading has 4 parts: the profile query, the conversion through text or a type branch, the counter for what failed to convert, and one sentence noting that a typed column written once upstream saves every consumer from repeating this logic.

About this lesson

This section is part of the Semi-Structured Data: Intermediate 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.