Reading a Nested Path Expression

Concepts covered: sqlJsonExtract

Three gotchas trip people up when working with semi-structured columns. Each has a specific failure mode and a known mitigation. Gotcha 1: missing keys return NULL silently If the path doesn't exist on a row (the device field is only present on mobile events, not web), extraction returns NULL. Aggregates that don't handle NULL produce wrong numbers (AVG skips NULL rows; COUNT(field) counts non-NULL only). Always check whether the path is consistently populated; if not, use COALESCE for defaults or filter out the NULL rows depending on intent. Gotcha 2: type coercion errors Casting an extracted value to a numeric type fails when the value isn't a number ('N/A' string, empty string). Postgres throws; Snowflake's TRY_CAST returns NULL; BigQuery's SAFE_CAST returns NULL. Use the safe variant w

About This Interactive Section

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.