문자열에서 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처럼 점 . 표기법으로 이루어진 식을 검색합니다.

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

반환되는 값은 기본 Athena 데이터 유형이 아닌 JSON 인코딩 문자열입니다.

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

JSON 문자열에서 스칼라 값을 추출하려면 json_extract_scalar(json, json_path) 함수를 사용합니다. 이것은 json_extract와 비슷하지만 JSON 인코딩 문자열 대신 varchar 문자열 값을 반환합니다. json_path 파라미터의 값은 스칼라(부울, 숫자 또는 문자열)여야 합니다.

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     |
+--------------+