Mirth Connect channels using SSL certs by cert

mc_certs.sql

with channel_xml as (
    select
        name,
        xmlparse(document channel) as channel_xml
    from channel c
)
, destination_connector_xml as (
    SELECT
        name as channel_name,
        unnest(xpath('//destinationConnectors/connector/name/text()', channel_xml))::TEXT as connector_name,
        unnest(xpath('//destinationConnectors/connector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
, source_connector_xml as (
    SELECT
        name as channel_name,
        'Source' as connector_name,
        unnest(xpath('//sourceConnector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
, all_connector_xml AS (
    SELECT * FROM source_connector_xml
     UNION ALL
     SELECT * FROM destination_connector_xml
)
, rows as ( select
                 unnest(xpath('//trustedCertificates/trustCACerts/text()', connector_xml))::TEXT as trust_ca_certs,
                 unnest(xpath('//trustedCertificates/trustedCertificateAliases/string/text()', connector_xml))::TEXT as trusted_cert_alias,
                 unnest(xpath('//localCertificateAlias/text()', connector_xml))::TEXT as private_alias,
                 unnest(xpath('//keyAlias/text()', connector_xml))::TEXT as interop_alias
                 , channel_name::TEXT as channel_name
                , connector_name::TEXT as connector_name
             from all_connector_xml
            ORDER BY channel_name ASC,
                     connector_name ASC
)
, rows_by_trust_alias as (
SELECT 
   --json_agg(row_to_json(rows.*)) 
coalesce(trusted_cert_alias, 'NO TRUSTED CERTS ONLY PRIVATE') as trusted_cert_alias, 
array_agg(distinct channel_name) as channels, 
array_agg(distinct connector_name) as connectors, 
array_agg(distinct private_alias) as private_aliases, 
array_agg(distinct interop_alias) as interop_aliases, 
array_agg(distinct trust_ca_certs) as trusted_ca_certs
   FROM "rows"
WHERE trusted_cert_alias IS NOT NULL OR private_alias IS NOT NULL OR interop_alias IS NOT null
group by 1)
select 
--json_agg(json_build_object(trusted_cert_alias, to_jsonb(rows_by_trust_alias) - 'trusted_cert_alias'))
json_object_agg(ta.trusted_cert_alias, to_jsonb(ta.*) - 'trusted_cert_alias')
from rows_by_trust_alias ta;
;