Postgres | How to extract a value from JSON array that contains multiple JSON objects

Question

I have a table that holds a column called additional_info. this column contains a JSON object that looks like that:

    {
        "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": ""
        }]
    }

I want to extract (to select) the value of "database" where srcIp equals 10.10.10.68. meaning I want to extract the value "xe" from the first JSON object under the JSON array called dbSources.

The only thing that I could do is

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

but how can I continue from there?

Postgres and the version is 12.3

Tal Levi
– Tal Levi

2021-10-05 08:04:07 +00:00
Commented Oct 5, 2021 at 8:04 — Tal Levi
– Tal Levi, Commented Oct 5, 2021 at 8:04

Philippe · Accepted Answer · 2021-10-05 08:27:44Z

You can do something like that:

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'

user330315 · Accepted Answer · 2021-10-05 09:48:40Z

1

You can use a JSON path query:

select jsonb_path_query_first(additional_info, '$.dbSources[*] ? (@.srcIp == "10.10.10.68").database')
from the_table

This assumes the column is of type jsonb (which it should be). If it's not, you need to cast it: additional_info::jsonb

answered Oct 5, 2021 at 9:48

user330315

Comments

raven 97 · Accepted Answer · 2021-10-05 11:40:11Z

1

For a table with name table_name, this query will extract the value of database whose srcIp is '10.10.10.68'

SELECT obj::json#>'{database}' as db_name FROM   edisondb.demo r, json_array_elements(r.some_text::json#>'{dbSources}') obj WHERE  obj->>'srcIp' = '10.10.10.68';

answered Oct 5, 2021 at 11:40

raven 97

316 bronze badges

Collectives™ on Stack Overflow

Postgres | How to extract a value from JSON array that contains multiple JSON objects

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related