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?
CodePudding user response:
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'
CodePudding user response:
Assuming the json is stored as json
you can use json functions such as json_array_elements
to convert the array into rows, the locate the row containing desired name-value:
select t.id, a.v->>'database'
from t
cross join lateral json_array_elements(t.additional_info->'dbSources') as a(v)
where a.v->>'srcIp' = '10.10.10.68'
CodePudding user response:
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