sql - Postgres |如何从包含多个 JSON 对象的 JSON 数组中提取值

Question

我有一个表，其中包含一个名为 additional_info 的列。此列包含一个如下所示的 JSON 对象:

    {
        "dbSources": [{
            "destIp": "10.10.10.29",
            "serviceType": "PostgreSql",
            "srcIp": "10.10.10.68",
            "database": "xe",
            "clusterMember": "",
            "dbId": "PostgreSql_10.10.10.29",
            "clusterName": "",
            "host": "",
            "dbUser": "system",
            "osUser": "",
            "userType": "Unknown",
            "srcApp": ""
        },{
            "destIp": "10.10.10.29",
            "serviceType": "PostgreSql",
            "srcIp": "10.10.10.69",
            "database": "xe1",
            "clusterMember": "",
            "dbId": "PostgreSql_10.10.10.29",
            "clusterName": "",
            "host": "",
            "dbUser": "system",
            "osUser": "",
            "userType": "Unknown",
            "srcApp": ""
        }]
    }

我想提取(选择)“database”的值，其中 srcIp 等于 10.10.10.68。意思是我想从名为 dbSources 的 JSON 数组下的第一个 JSON 对象中提取值“xe”。

我唯一能做的就是

    select additional_info::json ->'dbSources' as db from table

但是我该如何继续呢？

Answer 1

你可以这样做:

with query as (
select j->>'database' as db,j->>'srcIp' as src_ip from json_array_elements('{
        "dbSources": [{
            "destIp": "10.10.10.29",
            "serviceType": "PostgreSql",
            "srcIp": "10.10.10.68",
            "database": "xe",
            "clusterMember": "",
            "dbId": "PostgreSql_10.10.10.29",
            "clusterName": "",
            "host": "",
            "dbUser": "system",
            "osUser": "",
            "userType": "Unknown",
            "srcApp": ""
        },{
            "destIp": "10.10.10.29",
            "serviceType": "PostgreSql",
            "srcIp": "10.10.10.69",
            "database": "xe1",
            "clusterMember": "",
            "dbId": "PostgreSql_10.10.10.29",
            "clusterName": "",
            "host": "",
            "dbUser": "system",
            "osUser": "",
            "userType": "Unknown",
            "srcApp": ""
        }]
    }'::json->'dbSources') as j)
select db from query where src_ip = '10.10.10.68'