Pulling a Scalar Field Out of JSON

Concepts covered: sqlJsonExtract

JSON payloads are nested. A user object contains a profile object which contains an address object. Events have nested device, session, and context structures. The extraction path walks the nesting; each step is one property navigation. Arrays inside the path are referenced by index. Nested paths Two operators in Postgres: -> vs ->> Postgres has two extraction operators. -> returns a JSON value (which can be further navigated). ->> returns a text value (which cannot be further navigated). When walking a nested path, use -> for the intermediate steps and ->> only at the final step. payload->'device'->>'platform' navigates into the device object (->) then extracts the platform field as text (->>). Using ->> at an intermediate step fails because text cannot be navigated further. State this di

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.