Skip to content

Instantly share code, notes, and snippets.

  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save alfredrumss/2825008f0932c51b0d52e566a8ebc2fa to your computer and use it in GitHub Desktop.
arrays aggregation with null or {} (empty) values
SELECT array_agg(DISTINCT cis.active_cert_record.hash) AS hash,
array_agg(DISTINCT cis.active_cert_record.domain_name) AS domain_name,
array_agg(DISTINCT cis.active_cert_record.protocol_version) AS protocol_version,
cis.active_cert_record.subject_organization,
cis.active_cert_record.subject_organizational_unit,
cis.active_cert_record.subject_common_name,
cis.active_cert_record.signature
FROM cis.active_cert_record
WHERE CAST(md5(cis.active_cert_record.signature) AS UUID) = 'xxxxx-xxx-xxxx-xxxx'
AND cis.active_cert_record.protocol_version <> '{}'
GROUP BY cis.active_cert_record.subject_organization, cis.active_cert_record.subject_organizational_unit,
cis.active_cert_record.subject_common_name, cis.active_cert_record.signature
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment