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 데이터가 들어가 있다고 가정해봅시다.

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}]}

그럼 리스트가 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

그럼 리스트 안에 있는 데이터를 가져오려면 어떻게 해야 할까요?
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

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
