Extracting Nested Fields

Harbr's logistics pipeline ingests real-time shipment events from carrier APIs into the logistics.delivery_events table. Each event stores structured carrier and location data as a JSON payload in the event_payload VARIANT column.

The data team needs to extract specific fields from these payloads - carrier name, tracking reference, and destination city - so they can surface exception events for operational review. Your task is to query this semi-structured data and identify which carrier and city are associated with a specific shipment exception.