Output

SELECT
  JSON_QUERY(data, '$._metadata.jsRuntime') AS _metadata_jsRuntime,
  JSON_QUERY(data, '$._metadata.nodeVersion') AS _metadata_nodeVersion,
  NULL AS context_geolocation,
  NULL AS context_ip,
  JSON_QUERY(data, '$.contextlibrary.name') AS context_library_name,
  JSON_QUERY(data, '$.contextlibrary.version') AS context_library_version,
  JSON_QUERY(data, '$.event') AS event,
  JSON_QUERY(data, '$.messageId') AS messageId,
  JSON_QUERY_ARRAY(data, '$.properties.products') AS properties_products,
  JSON_QUERY(data, '$.timestamp') AS TIMESTAMP,
  JSON_QUERY(data, '$.type') AS type,
  JSON_QUERY(data, '$.userId') AS userId
FROM
  `projectid.datasetid.table`