Cacher is the code snippet organizer for pro developers

SELECT
  DISTINCT
  domain,
  COALESCE(
    NULLIF(data->>"$.AFFID", 'null'),
    NULLIF(data->>"$.AFID", 'null'),
    NULLIF(data->>"$.affId", 'null'),
    NULLIF(data->>"$.crm_affcode", 'null'),
    NULLIF(data->>"$.netid", 'null'),
    NULLIF(data->>"$.net_aff", 'null')
  ) as affid,
  COALESCE(
    NULLIF(data->>"$.subid1", 'null'),
    NULLIF(data->>"$.subid1", 'null')
  ) as subid1,
  COALESCE(
    NULLIF(data->>"$.email", 'null'),
    NULLIF(data->>"$.emailAddress", 'null'),
    NULLIF(data->>"$.shipping_email", 'null')
  ) as email,
  COALESCE(
    NULLIF(data->>"$.phone", 'null'),
    NULLIF(data->>"$.phoneAddress", 'null'),
    NULLIF(data->>"$.shipToPhone", 'null'),
    NULLIF(data->>"$.shipPhone", 'null'),
    NULLIF(data->>"$.shipping_phone", 'null')
  ) as phone,
  COALESCE(
    NULLIF(data->>"$.firstname", 'null'),
    NULLIF(data->>"$.first_name", 'null'),
    NULLIF(data->>"$.shipping_firstname", 'null'),
    NULLIF(data->>"$.shipToFirstName", 'null'),
    NULLIF(data->>"$.firstName", 'null')
  ) as firstname,
  COALESCE(
    NULLIF(data->>"$.lastname", 'null'),
    NULLIF(data->>"$.last_name", 'null'),
    NULLIF(data->>"$.shipping_lastname", 'null'),
    NULLIF(data->>"$.shipToLastName", 'null'),
    NULLIF(data->>"$.lastName", 'null')
  ) as lastname,
  COALESCE(
    NULLIF(data->>"$.address1", 'null'),
    NULLIF(data->>"$.shipping_address", 'null'),
    NULLIF(data->>"$.shipping_address1", 'null'),
    NULLIF(data->>"$.shipToAddress1", 'null'),
    NULLIF(data->>"$.shipAddress1", 'null')
  ) as address1,
  COALESCE(
    NULLIF(data->>"$.state", 'null'),
    NULLIF(data->>"$.shipping_state", 'null'),
    NULLIF(data->>"$.shipToState", 'null'),
    NULLIF(data->>"$.shipState", 'null')
  ) as state,
  COALESCE(
    NULLIF(data->>"$.city", 'null'),
    NULLIF(data->>"$.shipping_city", 'null'),
    NULLIF(data->>"$.shipToCity", 'null'),
    NULLIF(data->>"$.shipCity", 'null')
  ) as city,
  COALESCE(
    NULLIF(data->>"$.country", 'null'),
    NULLIF(data->>"$.shipping_country", 'null'),
    NULLIF(data->>"$.shipToCountry", 'null'),
    NULLIF(data->>"$.shipCountry", 'null')
  ) as country
#   ripped_partials.*
FROM ripped_partials
WHERE created_at > '2019-08-08 00:00:00'
HAVING email IS NOT NULL OR phone IS NOT NULL
ORDER BY email
;