PostgreSQL如何合并重复行的alt_names列并输出JSON结果

I get exactly what you're dealing with—duplicate rows in your certificate table where every field except alt_names is identical, and you need to roll those duplicates into a single row with all alt_names values packed into an array, formatted to match the specific JSON structure you shared.

Your original query was trying to aggregate all rows directly, but since most fields are duplicates, we need to group by those duplicate fields first before aggregating the alt_names values. Here's the corrected query that does exactly what you need:

SELECT array_to_json(array_agg(row_to_json(t)))
FROM (
    SELECT
        x509_commonName(c.certificate) as common_name,
        x509_issuerName(c.certificate) as issuer_name,
        x509_notBefore(c.certificate) as not_before,
        x509_notAfter(c.certificate) as not_after,
        x509_keyAlgorithm(c.certificate) as key_algorithm,
        x509_keySize(c.certificate) as key_size,
        x509_serialNumber(c.certificate) as serial_number,
        x509_signatureHashAlgorithm(c.certificate) as signature_hash_algorithm,
        x509_signatureKeyAlgorithm(c.certificate) as signature_key_algorithm,
        x509_subjectName(c.certificate) as subject_name,
        x509_name(c.certificate) as name,
        array_agg(DISTINCT x509_altNames(c.certificate)) as alt_names -- Aggregate alt names into array; DISTINCT removes duplicates if needed
    FROM certificate c
    WHERE c.id = '$1'
    GROUP BY
        x509_commonName(c.certificate),
        x509_issuerName(c.certificate),
        x509_notBefore(c.certificate),
        x509_notAfter(c.certificate),
        x509_keyAlgorithm(c.certificate),
        x509_keySize(c.certificate),
        x509_serialNumber(c.certificate),
        x509_signatureHashAlgorithm(c.certificate),
        x509_signatureKeyAlgorithm(c.certificate),
        x509_subjectName(c.certificate),
        x509_name(c.certificate)
) t;

Key Changes Breakdown: #

• GROUP BY Clause: We group by every field except alt_names—this tells PostgreSQL to combine all rows where these fields match into a single group, eliminating the duplicates you're seeing.
• array_agg for alt_names: Instead of selecting x509_altNames directly, we use array_agg() to collect all unique (optional, via DISTINCT) alt name values from the grouped rows into a single array.
• JSON Conversion: The outer row_to_json() converts each grouped row into a JSON object, and array_to_json() wraps that single object into a JSON array—perfectly matching the output format you specified.

Expected Output: #

This query will return exactly the JSON structure you provided, with all alt_names values aggregated into an array in a single object inside a JSON array:

[
  {
    "common_name": "www.leagueoflegends.com",
    "issuer_name": "C=US, O=GeoTrust Inc., CN=GeoTrust SSL CA - G3",
    "not_before": "2016-03-24T00:00:00",
    "not_after": "2017-03-24T23:59:59",
    "key_algorithm": "RSA",
    "key_size": 2048,
    "serial_number": "\x5cbeb7904e749cd466f1167bcd922ef0",
    "signature_hash_algorithm": "SHA-256",
    "signature_key_algorithm": "RSA",
    "subject_name": "C=US, ST=California, L=Los Angeles, O=\"Riot Games, Inc.\", CN=www.leagueoflegends.com",
    "name": "\x3075310b3009060355040613025553311330110603550408130a43616c69666f726e6961311430120603550407140b4c6f7320416e67656c657331193017060355040a141052696f742047616d65732c20496e632e3120301e06035504031417777772e6c65616775656f666c6567656e64732e636f6d",
    "alt_names": ["bertha.leagueoflegends.com", "battlegrounds.ru.leagueoflegends.com", ...]
  }
]

This output is ready to be consumed directly by your Python script—no extra parsing or manipulation needed beyond standard JSON handling.

内容的提问来源于stack exchange，提问作者0xroot