Postgres - 循环访问嵌套的、不一致的 Jsonb 数组和提取值，其中另一个键 = "X"

Question

我有一个jsonb对象，看起来像:

{"result" : {
"address_components": [
{
"types": ["route"],
"long_name": "Church Road",
"short_name": "Church Rd"
},
{
"types": ["administrative_area_level_1","political"],
"long_name": "England",
"short_name": "England"
},
{
"types": ["country", "political"],
"long_name": "United Kingdom",
"short_name": "GB"
},
{
"types": [ "postal_code"],
"long_name": "TS18 1TW",
"short_name": "TS18 1TW"
}
]
}

我试图写一个SELECT查询，通过types ->> 0作为列名，long_name作为值，但不是每个"类型"。因此，例如，如果我只想要'route'， 'postal_code'和'country'，结果可能看起来像:

route       | postal_code | country
Church Road | TS18 1TW.   | United Kingdom

我试过使用jsonb_array_elements，但这只返回第一个子数组。增加复杂性的是address_componentsjson并不总是包含即route数组，所以我不能只是通过即第一个数组并将其命名为"路由"。

db_fiddle

当然，这还远未完成，但我不确定从这里要去哪里。我如何制定"路线"?列名和"教堂路";价值，也为"国";和";postal_code"如果它们存在?

Answer 1

最好的方法可能是升级到v12，然后使用jsonpath方法，如。

jsonb_path_query_first(
full_response,
'$.result.address_components[*] ? (@.types[0]=="route").long_name'
)

例如:https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/2633