Extract device from JSON

Return event_id, device, and campaign extracted from the payload JSON. Default a missing campaign to 'unknown'. Order by event_id.

json_extract(payload, '$.device') AS device; COALESCE(json_extract(payload, '$.campaign'), 'unknown') AS campaign.

Sample input

eventsEvents 4 and 6 have no campaign key, so it defaults to unknown.

event_id	payload
1	{"device":"ios","campaign":"winter"}
3	{"device":"web","campaign":"spring"}
4	{"device":"web","items":["SKU-C"]}
6	{"device":"ios"}

Expected output

Expected outputdevice and campaign as columns; a missing campaign becomes unknown.

event_id	device	campaign
1	ios	winter
2	ios	winter
3	web	spring
4	web	unknown
5	android	winter
6	ios	unknown

Constraints

Use json_extract(payload, '$.device') and COALESCE(json_extract(payload, '$.campaign'), 'unknown'). Alias them device and campaign. Order by event_id.

Expected skills

JSON path extraction, aliasing, and defaulting missing keys with COALESCE.

SQL

AI evaluation

Run the SQL query to inspect preview rows.