從字串擷取 JSON 資料 - Amazon Athena
範例：擷取屬性

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

從字串擷取 JSON 資料

您可能會有來源資料，其中包含您不想要還原序列化到 Athena 中的資料表、以 JSON 編碼的字串。在這種情況下，您仍然可以使用 Presto 中提供的 JSON 函數來對此資料執行 SQL 操作。

將此 JSON 字串做為範例資料集。

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

範例：擷取屬性

若要從 JSON 字串擷取 name 和 projects 屬性，請使用 json_extract 函數，如以下範例所示。json_extract 函數會取得包含 JSON 字串的資料欄，並使用具有句點 . 表示法、類似 JSONPath 的表達式來搜尋它。

注意

JSONPath 會執行簡單的樹狀目錄周遊。它使用 $ 符號來表示 JSON 文件的根目錄，接著是一個句點和一個元素，直接在根目錄形成巢狀，例如 $.name。

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

傳回的值是一個 JSON 編碼字串，而不是原生 Athena 資料類型。

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

若要從 JSON 字串擷取純量值，請使用 json_extract_scalar(json, json_path) 函數。它類似於 json_extract，但會傳回varchar字串值，而不是 JSON 編碼的字串。json_path 參數的值必須是純量 （布林值、數字或字串）。

注意

請勿在陣列、地圖或結構上使用 json_extract_scalar 函數。

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

此查詢會傳回：

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

若要取得範例陣列中 projects 屬性的第一個元素，請使用 json_array_get 函數，並指定索引位置。

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

它會傳回在以 JSON 編碼的陣列中指定索引位置的值。

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

若要傳回 Athena 字串類型，請在 JSONPath 表達式內使用 [] 運算子，然後使用 json_extract_scalar 函數。如需有關 [] 的詳細資訊，請參閱 存取陣列元素。

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

它會傳回此結果：

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+