AWS Athena(Presto SQL) - json 데이터에서 value값 추출하기(json_extract, json_extract_scalar)

aws 아테나에서 json 데이터에 있는 value값들을 가져오는 방법에 대해서 알아보도록 합시다.

 

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

SELECT *
FROM dataset

 

위의 json 데이터가 들어가 있다고 가정해봅시다.

 

json 데이터

 

1. json_extract_scalar: 단순 value 값 가져오기

 

json_extract_scalar는 단순 key와 value의 관계일 때 값을 가져오는 함수입니다.

 

문법

- json_extract_scalar(변수,'$.원하는_변수_이름')

 

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

SELECT -- name은 key와 value의 관계
       json_extract_scalar(blob,'$.name') as name,
       
       -- projects는 key와 value_list와의 관계
       json_extract_scalar(blob,'$.projects') as projects
       
FROM dataset

 

- name은 단순 key와 value의 관계여서 Susan Smith라는 값을 뱉지만

 {"name": "Susan Smith"}

 

- projects는 value가 리스트이기 때문에 값을 반환하지 않습니다.

{"projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}

name은 값을 반환했고 projects는 반환하지 않았다.

그럼 리스트가 value인 값을 가져오려면 어떻게 해야 할까요?

 

2. json_extract: value값이 리스트로 된 데이터 가져오기

 

json_extract는 key와 value리스트의 관계일 때 값을 가져오는 함수입니다.

 

문법

- json_extract(변수, '$.원하는_변수_이름')

 

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

SELECT json_extract(blob,'$.projects') as projects
FROM dataset

 

project 리스트를 반환했다!

그럼 리스트 안에 있는 데이터를 가져오려면 어떻게 해야 할까요?

 

3. 리스트 안에 있는 값 가져오기

json_extract_scalar 안에 $표시에서 해당 부분을 처리할 수 있습니다.

 

문법

- json_extract_scalar(변수, '$.원하는_변수_이름[원하는 리스트 위치].원하는 변수')

 

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

SELECT 
       -- blob데이터에서 projects 리스트에서 첫번째 name을 가져와라
       json_extract_scalar(blob,'$.projects[0].name') as name1,
       
       -- blob데이터에서 projects 리스트에서 두번째 name을 가져와라
       json_extract_scalar(blob,'$.projects[1].name') as name2
FROM dataset

 

리스트에서 각각 json 안에 있는 name을 추출했다.

 

4.  이중으로 json이 덮인 데이터에서 value 가져오기

 

해당 부분도 json_extract_scalar 안에 $표시에서 해당 부분을 처리할 수 있습니다.

 

문법

- json_extract_scalar(변수, '$.원하는_변수_이름.원하는 변수')

 

그냥 리스트 인덱스만 사라진 것이라고 보시면 됩니다.

 

org 데이터를 보면 value에 리스트가 아닌 json이 들어가 있습니다.

{"org": {"name":"engineering"}}

 

해당 데이터는 쿼리처럼 구할 수 있습니다.

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

SELECT -- org.name으로 org안에 name안에 있는 value값을 가져올 수 있습니다.
       json_extract_scalar(blob,'$.org.name') as name
FROM dataset

json안에 json이 들어가있어도 추출 할 수 있다.