时间戳

首页 > 解决方案 > PSQL：如何成功提取信息并收集特定列中的所有值

sql - PSQL：如何成功提取信息并收集特定列中的所有值

问题描述

我正在努力解决这样一种情况，即我有多行重复相同的信息，但其中一列除外：

  {
    "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": "\\x3075310b3009060355040613025553311330110603550408130a43616c69666f726e6961311430120603550407140b4c6f7320416e67656c657331193017060355040a141052696f742047616d65732c20496e632e3120301e060355040314177777772e6c65616775656f666c6567656e64732e636f6d",
    "alt_names": "bertha.leagueoflegends.com"
  },
  {
    "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": "\\x3075310b3009060355040613025553311330110603550408130a43616c69666f726e6961311430120603550407140b4c6f7320416e67656c657331193017060355040a141052696f742047616d65732c20496e632e3120301e060355040314177777772e6c65616775656f666c6567656e64732e636f6d",
    "alt_names": "battlegrounds.ru.leagueoflegends.com"
  },

如您所见，唯一返回不同内容的字段是alt_names.

我想知道是否有可能获取alt_names数组中的所有值，然后返回包含剩余列的单行，将数组追加到alt_names.

最终我想将查询输出格式化为 JSON，如下所示：

[
  {
    "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": "\\x3075310b3009060355040613025553311330110603550408130a43616c69666f726e6961311430120603550407140b4c6f7320416e67656c657331193017060355040a141052696f742047616d65732c20496e632e3120301e060355040314177777772e6c65616775656f666c6567656e64732e636f6d",
    "alt_names": "www.leagueoflegends.com", "xxx", "yyy", "zzz", ...
  }
]

有人可以在这里帮助我吗？

编辑：添加更多信息：

这是我的查询当前的样子：

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,
                    x509_altNames(c.certificate) as alt_names
        FROM certificate c
                WHERE c.id = '$1') t

不幸的是，我不能使用array_agg从函数返回的结果，因此我正在寻找另一种选择。

另外，我不希望将其存储到另一列中，而是只想接收 JSON 格式的输出并通过 python 上的脚本对其进行处理。

谢谢

标签： sqlpostgresql

解决方案

所以我最终解决了这个问题，如下所示：

SELECT row_to_json(u)
        FROM (
            SELECT 
                x509_commonName(c.certificate),
                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,
                (
                    SELECT array_to_json(ARRAY(SELECT x509_altNames(c.certificate)
                    FROM certificate c
                    WHERE c.id = '$1'))
                )
            FROM certificate c
            WHERE c.id = '$1'
        ) u

推荐阅读