postgresql - 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
Church Road | TS18 1TW.   | United Kingdom

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

db_fiddle

当然，这还远未完成，但我不确定从这里去哪里。如何将“路线”设为列名，将“教堂路”设为值，以及“国家”和“邮政编码”（如果存在）？

标签： postgresql

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