[빅쿼리/SQL] JSON 파싱하기 (기본편)

빅쿼리에서 데이터마트를 통해 어느정도 정제된 데이터를 사용하지 않고, 그 앞단의 보다 Raw 한 데이터를 살펴보게 되면 { } 와 [ ]  로 묶여있는 낯선 형태의 데이터를 자주 접하게 되는데요.

이번 포스팅에서는 저렇게 묶여있는 JSON 형태의 데이터를 다루는 방법에 대해 살펴보려 합니다.

 

공식 문서는 링크에서 확인하실 수 있습니다!

JSON functions  |  BigQuery  |  Google Cloud

---

0. JSON....?

먼저 JSON 데이터를 파싱하는 방법을 살펴보기 앞서 JSON 형식의 데이터는 어떻게 구성되고, 왜 사용하는지를 알아보고 시작합시다.

 

0.1. JSON 이 뭐야?

일단 JSON 은 아래와 같이 정의가 됩니다.

JSON (JavaScript Object Notation)
- 텍스트를 활용하여 데이터를 저장하고 전송하는 데이터 공유를 위한 개방형 표준 파일 형식

즉, 어떤 데이터를 저장하고 공유하기 위한 다양한 방법 중 하나라고 생각하면 되는데요.

우리가 접하는 빅쿼리에서도 결국 데이터를 저장하기 위한 방법으로 JSON 형태를 선택하고 있는 것으로 생각할 수 있습니다.

 

 

0.2. JSON 을 왜 써?

그러면 '왜 우리가 흔히 알고 있는 예쁜 모양의 데이터로 저장을 하지 않고 복잡하게 생긴 JSON 형태로 데이터를 저장할까?'라는 생각을 할 수 있는데요.

JSON 형식으로 데이터를 적재하면 몇 가지 장점이 있습니다.

1. JSON 형식으로 묶여있는 다양한 정보를 하나의 컬럼으로 적재할 수 있다.
2. Format이 고정되지 않은 반정형 데이터를 저장할 수 있다.

즉, 데이터를 저장함에 있어서 유연성을 가질 수 있다는 큰 장점이 있기 때문에 JSON 형식으로 데이터를 적재하고 사용한다고 이해해볼 수 있습니다.

 

 

---

1. JSON 구조 살펴보기

JSON 형식의 데이터를 왜 사용하는지 알아봤으니 다음으로는 JSON 형식이 어떻게 생겼는지를 알아봅시다.

이 구조를 알아야 어떤 원리로 JSON 데이터를 파싱할 수 있을지 쉽게 생각해낼 수 있을테니깐요.

 

1.1. JSON 의 기본 구조

기본적으로 JSON 은 다음과 같은 형식으로 구성이 됩니다.

한 쌍의 Key 값과 Value 값이 {  } 로 묶여있는 형태 

 

빅쿼리 콘솔에서 살펴보면 이렇게 생긴 녀석들입니다. (임시로 하나 만들어 봤습니다.)

이렇게 살펴보면 정말 별거 아닌것 같지 않나요?

제 생각엔 낯선 형태의 데이터가 눈에 보이고, 구조가 눈에 잘 들어오지 않기 때문에 JSON 형식의 데이터가 어렵다고 느껴지는 것 같습니다.

 

1.2. 배열 형식 (Array)

그런데 사실 JSON 형태의 데이터를 접해보면 Array 라는 녀석을 높은 확률로 마주치게 됩니다.

그래서 Array 에 대해서도 알고 있어야 되는데요.

 

기본적으로 Array의 생김새는 단순합니다.

[ ] 안에 여러 데이터가 , 로 연결되어있는 형태

 

빅쿼리 콘솔에서 마주치면 아래와 같이 보이는데요.

{ } 로 묶여있는 JSON 형식의 데이터 안에 [ ] 로 묶여있는 Array 가 들어있는 것을 확인할 수 있습니다.

 

아까보다 조금 복잡해보이긴 하지만... 구조에 대한 이해를 하고 있다면 어떤 어려운 JSON 형식의 데이터도 무리없이 파싱할 수 있다고 생각이 됩니다!

앞으로 우리가 접할 JSON 형태의 데이터들은 위 구조의 다양한 조합일 뿐이니깐요.

---

위 2가지 구조만 잘 알고 있다면 아래의 JSON 형식의 데이터의 구조도 어렵지 않게 살펴볼 수 있을거라 생각합니다.

JSON 안에 Array 가 들어있는데 그 Array 안에는 또 JSON 형식으로 묶여있는 이런 데이터도.... (혐오)

 

---

2. JSON 파싱하기

지금까지 JSON 데이터의 구조에 대해서 살펴봤으니 이제 JSON 데이터에서 우리가 원하는 데이터만 파싱하는 방법을 알아봅시다.

 

2.1. JSONPath 알아두기

빅쿼리에서 제공하는 JSON 함수를 사용하기 앞서 JSONPath 라는 것을 알아두고 가야합니다.

JSONPath	설명
$	루트 개체, JSON 데이터를 추출할 때 이 연산자로 시작을 해야 함 (안그러면 에러 발생)
.	JSON 데이터의 Key 에 해당하는 부분을 가져올 때 사용 = 하위 속성을 가져올 때 사용
[ n ]	JSON 데이터에 들어있는 배열(Array)의 n 번째 값을 가져올 때 사용

이 JSONPath 값들은 아래의 JSON 파싱 함수와 함께 사용하게 됩니다.

 

2.2. JSON 파싱 함수

빅쿼리에서는 JSON 데이터를 파싱하는 함수를 제공합니다.

위 4가지 함수를 활용해서 JSON 데이터를 파싱하면 되는건 알겠는데 저는 설명이 너무 와닿지가 않더라구요..😥

그래서 하나씩 뜯어보기로 했습니다.

 

일단 실습을 위한 예시 데이터는 그냥 제가 임시로 만들었구요.

아래 코드를 빅쿼리에서 사용하시면, 첨부된 사진의 결과를 얻을 수 있습니다.

select JSON '{"name": "godong", "birth": {"year":1995, "month":11, "day":13}, "favorite_color":["green", "blue"], "recent_purchase":[{"product": "[할리 오브 스코틀랜드] 쉐기독 니트", "price": 200000}, {"product": "[세인트제임스] 봉봉 맨투맨", "price": "121000"}]}' as json_sample

 

1) JSON_QUERY, JSON_VALUE 

우선 JSON_QUERY와 JSON_VALUE 함수는 JSON 데이터에서 어떠한 스칼라 값을 가져오기 위해 사용됩니다.

스칼라 값은 하나의 값(단일 값)이라고 이해하고 넘어가면 될 것 같습니다.

예시와 함께 살펴보시죠.

 

예시 데이터에서 name 에 해당하는 값을 가져오고 싶다고 합시다.

(위 예시 데이터를 담은 임시 테이블의 이름을 json_test_tb 라고 지정하고 앞으로 사용하겠습니다.)

select 
  json_value(json_sample, '$.name') as s_name_str
  , json_query(json_sample, '$.name') as s_name_json

from json_test_tb

그럼 각각 위의 값이 추출이 되는데요.

차이점을 살펴보면 JSON_VALUE 함수를 사용했을 땐 따옴표 없이 값이 추출되었고,

JSON_QUERY 함수를 사용한 경우 따옴표와 함께 추출이 되었다는 것을 알 수 있습니다.

 

단순히 따옴표의 유무라고 생각할 수 있지만 두 함수 결과값의 차이는 반환 유형이 다르다는 것입니다.

• JSON_VALUE 반환 데이터 = STRING 
• JSON_QUERY 반환 데이터 = STRING 형식의 JSON

그래서 데이터 타입에 민감한 빅쿼리에서 WHERE 절로 위 함수를 사용할 땐 아래와 같은 상황이 발생할 수 있습니다.

select 
  json_value(json_sample, '$.name') as s_name_str
  , json_query(json_sample, '$.name') as s_name_json

from json_test_tb

-- where json_value(json_sample, '$.name') = 'sky' -- STRING = STRING 조건으로 제대로 실행 됨
-- where json_query(json_sample, '$.name') = 'sky' -- STRING = JSON 조건으로 에러 발생

그래서 JSON_QUERY 함수를 사용한 뒤 STRING 과의 비교 조건절을 사용하고 싶다면, 아래와 같이 결과값을 다시 STRING 으로 바꿔주는 과정이 필요합니다.

select 
  json_value(json_sample, '$.name') as s_name_str
  , json_query(json_sample, '$.name') as s_name_json

from json_test_tb

-- where json_value(json_sample, '$.name') = 'sky' -- STRING = STRING 조건으로 제대로 실행 됨
-- where json_query(json_sample, '$.name') = 'sky' -- STRING = JSON 조건으로 에러 발생
where STRING(json_query(json_sample, '$.name')) = 'sky' -- STRING = STRING 조건으로 제대로 실행 됨

 

그럼 왜 불편하게 JSON 형식으로 반환하는 JSON_QUERY 함수를 사용하는 걸까요?

이번에는 예시 데이터에서 birth 에 해당하는 데이터를 가져온다고 생각해봅시다.

select 
  json_value(json_sample, '$.birth') as s_birth_str
  , json_query(json_sample, '$.birth') as s_birth_json

from json_test_tb

JSON_VALUE 함수를 사용한 경우 결과값으로 null 이 출력되었습니다.

여기서 다시 한번 JSON 함수들의 설명을 살펴봅시다.

• JSON_QUERY 함수의 경우 반환 유형이 JSON 형태이며 결과값이 문자값이든, 정수이든, Bool 값이든 JSON 형태로 출력을 합니다.
  당연히 그렇기 때문에 결과값이 JSON 형태의 스칼라 값이더라도 문제 없이 값을 추출해낼 수 있습니다.
• 반면, JSON_VALUE 함수의 경우 스칼라 값으로 문자열, 정수, Bool 값만 나타낼 수 있으며 이 값들이 아니라면 null 값을 출력한다고 합니다.

그래서 위와 같이 JSON 형식이 중첩되어 있을 경우에는 JSON_VALUE 함수를 사용할 수 없고, JSON_QUERY 함수를 사용해야 합니다.

select 
  json_value(json_sample, '$.birth') as s_birth_str
  , json_query(json_sample, '$.birth') as s_birth_json

  , json_query(json_sample, '$.birth.day') as s_day_json -- JSON 형태
  , json_query(json_sample, '$.birth.month') as s_month_json -- JSON 형태
  , json_query(json_sample, '$.birth.year') as s_year_json -- JSON 형태

  , INT64(json_query(json_sample, '$.birth.day')) as s_day_int -- INT64 형태
  , INT64(json_query(json_sample, '$.birth.month')) as s_month_int -- INT64 형태
  , INT64(json_query(json_sample, '$.birth.year')) as s_year_int -- INT64 형태

from json_test_tb

 

 

2) JSON_VALUE_ARRAY, JSON_QUERY_ARRAY

다음으로는 JSON_QUERY_ARRAY 함수와ㅏ JSON_VALUE_ARRAY 함수에 대한 내용입니다.

두 함수의 차이는 앞서 JSON_QUERY 함수와 JSON_VALUE 함수를 살펴보며 알 수 있었던 반환 형태의 차이라고 생각해볼 수 있구요.

그럼 JSON_QUERY, JSON_VALUE 함수와 {}_ARRAY 함수가 무엇이 다른지를 살펴보는 흐름으로 진행해보겠습니다.

 

사실 함수 이름에서 눈치를 챌 수 있듯, 파싱하고자하는 값이 스칼라 값인지, 아니면 배열(array) 값인지에 따라 사용하는 함수가 달라질 뿐입니다.

그래서 예시 데이터의 값들을 {}_ARRAY 함수를 이용해서 모두 가져와본다면요.

 

< JSON_VALUE_ARRAY >

select 
  json_value_array(json_sample, '$.name') as name_value
  , json_value_array(json_sample, '$.birth') as birth_value
  , json_value_array(json_sample, '$.favorite_color') as f_color_value
  , json_value_array(json_sample, '$.recent_purchase') as r_purchase_value

from json_test_tb

 

< JSON_QUERY_ARRAY >

select 
  json_query_array(json_sample, '$.name') as name_query
  , json_query_array(json_sample, '$.birth') as birth_query
  , json_query_array(json_sample, '$.favorite_color') as f_color_query
  , json_query_array(json_sample, '$.recent_purchase') as r_purchase_query

from json_test_tb
;

JSON_VALUE_ARRAY 와 JSON_QUERY_ARRAY 함수를 비교해보며 살펴보면 아래와 같이 정리해볼 수 있습니다.

• JSON 데이터의 Key 값이 name, birth 의 경우 배열이 아니었기 때문에 추출된 값이 없음
• favorite_color 와 recent_purchase 의 경우에는 [ ] 로 둘러쌓여있는 배열값이기 때문에 값이 추출됨
• recent_purchase 의 경우, 배열 안에 JSON 형태의 값이 들어있기 때문에  JSON_QUERY_ARRAY 함수로만 파싱이 가능함

 

그런데 하나의 행에 여러 row 가 있는 요상한 형태가 눈에 보입니다.

이러한 형태를 배열(Array) 형태의 데이터라고 부르며,

이는 빅쿼리에서 볼 수 있는 특이한 형태의 구조로 하나의 행이지만 마치 여러 row 로 이루어져있는 형태를 가지고 있습니다.

 

이 부분을 각각의 행으로 떼어내고, 배열 형태에 들어있는 JSON 데이터를 다시 파싱하는 내용은 배열을 다루는 방법을 포스팅 한 뒤 다시 살펴보기로 하고, 지금은 파싱이 제대로 되었는지에 대한 부분만 살펴보고 넘어가도록 합시다.

 

 

---

3. 마치며

이렇게 이번에는 JSON 형태의 데이터를 파싱하는 방법에 대해서 살펴봤는데요.

기본적인 내용은 오늘 살펴본 내용으로 충분히 파싱이 가능하다고 생각하지만, 배열이 엮여있는 경우에는 조금 더 알아야하는 내용이 있네요.

 

다음 포스팅에서는 우선 빅쿼리의 배열(array) 데이터를 다루는 방식에 대해 살펴보고, 그 후에 배열 + JSON 데이터 파싱에 대해 살펴보는 흐름으로 가져가면 좋을 듯 합니다.

 

ARRAY + JSON 데이터를 다루는 방법 포스팅을 완료했습니다!

다음편은 아래 링크에서 확인하실 수 있습니다!

[빅쿼리/SQL] ARRAY 가 들어있는 JSON 데이터 파싱하기 (심화편)