GCP/BigQuery

BigQuery - JSON 컬럼 파싱하기

안녕하세요.

BigQuery를 사용하다 보면 자주 접하는 Column이 있습니다. 바로 JSON으로 만들어진 Column입니다.

데이터의 형태가 계속해서 변하거나 항목들이 가변적으로 들어오는 경우 JSON 형태로 데이터를 넣어서 데이터를 적재하는 경우가 많습니다. 데이터를 JSON으로 저장할 경우 파싱 해서 데이터를 꺼내서 써야 하는 경우가 많은데요. 이러한 기능을 지원하는 함수를 소개하고자 합니다. 저도 이번에 처음으로 써봐서 아주 신기해서 글을 정리하려고 합니다.

💁‍♂️ 들어가며 ...

들어가기 앞서 여러 종류의 JSON 파싱 함수를 이 페이지에서 볼 수 있습니다.

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ko#json_query

표준 SQL의 JSON 함수 | BigQuery | Google Cloud

BigQuery는 JSON 데이터를 검색하고 변환할 수 있는 다음 함수를 지원합니다. 함수 개요 표준 JSON 추출 함수(권장) 다음 함수는 큰따옴표를 사용하여 잘못된 JSONPath 문자를 이스케이프 처리합니다. "a

cloud.google.com

여러가지 함수가 있는데요. GCP에서는 다음의 4개의 함수를 권장합니다.

JSON 함수는 이외에 4개가 더 있지만 (아마도 구버전) 사용하기 약간 불편 한 감이 있습니다.

(개인적 생각입니다.)

매우 강력하고 이 함수를 통해서 다양한 부분을 파싱 가능합니다.

이 글에서는 권장하는 JSON 파싱 함수의 일부 기능을 사용해보려고 합니다.

🙋‍♂️ 이렇게 사용해 보세요.

JSON_QUERY(json_string_expr, json_path)

cs

일단 기본적인 문법은 위와 같습니다.

이렇게 보면 이해가 잘 안 갑니다. 예시를 통해서 확인하는 게 제일 좋은 거 같아서 바로 예시로 넘어가겠습니다.

(구글 예시는 봐도 잘 모르겠습니다...)

다음과 같이 JSON으로 이루어진 Column이 있고 이를 JSON_QUERY를 통해서 가져오겠습니다.

SELECT JSON_QUERY(json,'$')AS result_data
FROM 
(
 SELECT '{"name": "joo", "age": "34"}' AS json UNION ALL
 SELECT '{"name": "hyoung", "age": "20"}' AS json UNION ALL
 SELECT '{"name": "kwon", "age": "37"}' AS json 
)AS t
Colored by Color Scripter

cs

결과는 다음과 같이 나옵니다.

다음과 같이 $만 넣을 경우 전체 값을 불러옵니다.

일단 이 부분은 단순히 데이터를 가져오기만 하는 부분이고 다양한 형태로 파싱 해서 데이터를 가져올 수 있습니다. 아래의 내용을 보면 예시를 통해서 여러 가지 파싱을 볼 수 있습니다.

🙋‍♂️ 👉 JSON_QUERY를 통해서 요소 가져오기

SELECT JSON_QUERY(json,'$.name')AS result_data
FROM 
(
 SELECT '{"name": "joo", "age": "34"}' AS json UNION ALL
 SELECT '{"name": "hyoung", "age": "20"}' AS json UNION ALL
 SELECT '{"name": "kwon", "age": "37"}' AS json 
)AS t
Colored by Color Scripter

cs

아주 간단하게 요소를 가져올 수 있습니다.

처음 보여드린 전체를 가져오는 부분과 크게 달라진 게 없는 것 같지만 살짝 변경되었습니다.

JSON_QUERY(json,'$.name')

cs

$옆에. name이 붙었습니다. JSON에서 원하는 요소만 빼서 칼럼화 시켰습니다.

이름만 빼왔습니다. 결과는 위와 같습니다. 그런데, 보면 "으로 큰따옴표가 붙어있네요?

이 경우 JSON_VALUE를 통해서 딱 값만 빼올 수 있습니다.

SELECT JSON_VALUE(json,'$.name')AS result_data
FROM 
(
 SELECT '{"name": "joo", "age": "34"}' AS json UNION ALL
 SELECT '{"name": "hyoung", "age": "20"}' AS json UNION ALL
 SELECT '{"name": "kwon", "age": "37"}' AS json 
)AS t
Colored by Color Scripter

cs

이렇게 가져오면 " 쌍따옴표가 없이 데이터를 가져올 수 있습니다.

JSON_QUERY와 JSON_VALUE의 차이점을 보고 싶으실 경우 다음의 블로그에 잘 정리되어 있습니다.

https://mariadb.com/kb/en/differences-between-json_query-and-json_value/

Differences between JSON_QUERY and JSON_VALUE

Comparison between and examples with JSON_QUERY and JSON_VALUE.

mariadb.com

🙋‍♂️ 👉 그럼 형태가 다르면 어떻게 될까?

위에서 언급했듯이 데이터가 가변적으로 들어오는 부분 때문에 JSON을 많이 쓴다고 했는데요.

그러므로 데이터의 형태가 같을 순 없습니다. 예를 들면 다음과 같이 데이터의 key값이 어떤 것은 있고 어떤 것은 없고 일 경우입니다.

한번 위의 Query에서 name부분을 age로 변경하면 다음과 같이 나옵니다. 오류가 발생하지 않습니다.

NULL로 데이터를 표기합니다. key가 없기 때문에 위와 같이 NULL로 표시됩니다.

🤦‍♂️ 더욱 복잡한 데이터 파싱 해보기

🙋‍♂️ 👉 Dictionary 안쪽에 Dictionary를 가져오기

조금 더 복잡한 데이터를 한번 해볼까요?

예를 들면 다음과 같은 데이터입니다.

다음과 같이 항목에 쌓여있는 경우 다음과 같이 파싱이 가능합니다.

SELECT JSON_QUERY(json,'$.information')AS result_data
,json
FROM 
(
 SELECT '{"information":{"name": "joo","age":"34"},"hobby":{"name":"Soccer"}}' AS json UNION ALL
 SELECT '{"information":{"name": "hyoung", "age": "20"},"hobby":{"name":"Game"}}' AS json UNION ALL
 SELECT '{"information":{"name": "kwon", "age": "37"},"hobby":{"name":"Basketball"}}' AS json 
)AS t
Colored by Color Scripter

cs

데이터를 보시면 2번 감싸져 있습니다. 이 부분도 간단합니다. 이렇게 위와 같이 Query를 실행하면 다음과 같은 결과가 나옵니다.

여기서 다시 한번 데이터를 가져오려면 아주 간단하게 가능합니다.

SELECT JSON_QUERY(json,'$.information.name')AS result_data
,json
FROM 
(
 SELECT '{"information":{"name": "joo","age":"34"},"hobby":{"name":"Soccer"}}' AS json UNION ALL
 SELECT '{"information":{"name": "hyoung", "age": "20"},"hobby":{"name":"Game"}}' AS json UNION ALL
 SELECT '{"information":{"name": "kwon", "age": "37"},"hobby":{"name":"Basketball"}}' AS json 
)AS t
Colored by Color Scripter

cs

요소의 안쪽에서 요소를 가져오도록 할 수 있습니다.

참고로 JSON_VALUE를 통해서 $. information을 실행할 경우 값이 나오지 않습니다. 한번 해보시길...

🙋‍♂️ 👉 List 가져오기

그렇다면 이것 이외에 리스트도 가능할까?라는 궁금증으로 인해서 한번 해봤습니다.

List의 경우 Key가 없기 때문에 항목의 순서를 지정해야 합니다.

다음과 같이 Query를 실행하면 결과를 가져올 수 있습니다.

SELECT JSON_QUERY(json,'$[1]')AS result_data
,json
FROM 
(
 SELECT '[{"information":{"name": "joo","age":"34"},"hobby":{"name":"Soccer"}},{"memo":"hello"}]' AS json UNION ALL
 SELECT '{"information":{"name": "hyoung", "age": "20"},"hobby":{"name":"Game"}}' AS json UNION ALL
 SELECT '{"information":{"name": "kwon", "age": "37"},"hobby":{"name":"Basketball"}}' AS json 
)AS t
Colored by Color Scripter

cs

아래와 같은 결과를 얻을 수 있습니다.

물론 또다시 memo라는 요소를 빼오는 것도 가능합니다.

큰 문제없이 데이터를 가져올 수 있습니다.

🙋‍♂️ 마치며...

BigQuery에서 JSON 형태의 데이터를 다양한 방법으로 파싱 하는 방법을 알아봤는데, JSON_QUERY만 있어도 어느 정도 파싱이 가능한 것으로 보입니다. 워낙 데이터가 다양하다 보니 이것으로 안 되는 경우도 가끔 있긴 한데, 대부분 Query로 해결이 가능합니다.

혹시라도 하다가 막히는 부분이 있으면 댓글 부탁드립니다. 😄

🤷‍♂️ 참고