SET @@udf.properties = JSON ‘{“enableDebugOutput”: true}’; CREATE OR REPLACE TABLE `YOUR_PROJECT.YOUR_DATASET.clean_events` AS WITH sample_events AS ( SELECT ‘evt_001’ AS event_id, ‘{“user_id”: “usr_101”, “event”: “click”, “amount”: 25.5}’ AS raw_data UNION ALL SELECT ‘evt_002’ AS event_id, ‘corrupted_json{‘ AS raw_data UNION ALL — Syntax error SELECT ‘evt_003’ AS event_id, ‘{“event”: “view”}’ AS raw_data UNION ALL — Missing required user_id SELECT ‘evt_004’ AS event_id, ‘{“user_id”: “usr_102”, “event”: “checkout”, “amount”: 99.0}’ AS raw_data ), parsed_events AS ( SELECT event_id, `YOUR_PROJECT.YOUR_DATASET.safe_parse_json`(raw_data, ‘user_id’) AS validated_json FROM sample_events ) SELECT event_id, JSON_VALUE(validated_json, ‘$.user_id’) AS user_id, JSON_VALUE(validated_json, ‘$.event’) AS event_name, ‘VALID’ AS status FROM parsed_events WHERE validated_json IS NOT NULL;
