gistfile1.sql

UPDATE reports_clean_buffer
SET release_channel = json_object_field_text(rewrite, 'rewrite_build_type_to'),
    product_version_id = product_versions.product_version_id
FROM update_channel_map, product_versions
JOIN raw_crashes as rc on (uuid::uuid = rc.uuid)
WHERE product_versions.version_string = trim(both '"' from (rewrite->'Version')::text)
AND product_versions.product_name = 'B2G'
AND (rewrite->'Android_Manufacturer')::text = (rc.raw_crash->'Android_Manufacturer')::text
AND (rewrite->'Android_Model')::text = (rc.raw_crash->'Android_Model')::text
AND (rewrite->'Android_Version')::text = (rc.raw_crash->'Android_Version')::text
AND (rewrite->'B2G_OS_VERSION')::text = (rc.raw_crash->'B2G_OS_VERSION')::text
AND build IN
    (SELECT trim(both '"' from value::text)::numeric
     FROM json_array_elements(rewrite->'BuildID'))
AND (rewrite->'ProductName')::text = (rc.raw_crash->'ProductName')::text
AND (rewrite->'ReleaseChannel')::text = (rc.raw_crash->'ReleaseChannel')::text
AND (rewrite->'Version')::text = (rc.raw_crash->'Version')::text;