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

이번 포스팅에서는 지난번 포스팅에 이어 빅쿼리에서 JSON 형태의 데이터를 파싱 하는 방법을 계속해서 살펴보려 합니다.

지난번 글에서는 JSON 파싱 함수와 관련된 내용을 정리했다면 이번 포스팅에서는 ARRAY 가 함께 쓰인 JSON 데이터를 파싱 하는 방법에 대해 포스팅하려 하는데요.

만약 JSON 데이터에 익숙하지 않으시다면 지난 글을 읽어보시고 이 포스팅을 보시는 걸 추천드립니다!

[빅쿼리/SQL] JSON 형식의 데이터 파싱하기 (기본편)

 

 

---

0. ARRAY 형식의 데이터 다루기

먼저 ARRAY 형식의 데이터를 다루기 위해서 샘플로 사용할 데이터셋을 만들어봅시다.

          /* 1. 예시용 Sample Dataset 만들기 -> Array 형태를 만들기 위함 */

with json_test_tb as (
  
  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

)

select string(json_query(json_sample, '$.name')) as name
  , json_query(json_sample, '$.birth.day') as day
  , json_query(json_sample, '$.birth.month') as month
  , json_query(json_sample, '$.birth.year') as year
  , json_query_array(json_sample, '$.recent_purchase') as prd_list

from json_test_tb
;

위 쿼리를 실행하면 아래와 같은 결과물이 출력되는데요.

ARRAY 가 포함된 데이터 형식

우선 샘플로 사용할 데이터는 JSON 형태의 데이터를 파싱 한 뒤 ARRAY를 마주친 상황을 보여주고 있다고 생각하시면 될 것 같습니다.

 

위 사진을 보면 기존에 보던 데이터와는 조금 다르게 행은 1개이지만 2줄로 나오는 모습을 확인할 수 있는데요.

이러한 모습을 한 데이터 형태를 ARRAY라고 하며, ARRAY는 지난 포스팅에서 이야기했듯 JSON 형식의 데이터와 함께 많이 사용되기 때문에 ARRAY 형식을 알아둘 필요가 있습니다.

 

그러니 진행 순서를 ARRAY를 다루는 방법에 대해서 먼저 살펴본 뒤, JSON과 함께 쓰인 ARRAY 형식의 데이터를 다루는 방법으로 넘어가는 흐름으로 가져가보겠습니다.

(이번 포스팅에서는 단지 ARRAY 데이터를 파싱 하는 부분에 대해서만 다룰 예정이라 보다 다양한 ARRAY 데이터 관련 내용들은 빅쿼리 정식 문서를 참고해 주시면 감사하겠습니다!)

 

 

0.1. ARRAY 형식 단순 파싱하기

ARRAY 형식의 데이터를 파싱 하는 방법에는 아래와 같은 방식들이 존재합니다.

파싱 방법	사용 방법	예시	결과
[ ]	ARRAY 데이터 중 N+1 번째 데이터를 파싱할 때 사용	ARRAY[N]	N+1 번째 ARRAY 속 데이터가 파싱됨
[ safe_offset() ]	ARRAY 데이터 중 N+1 번째 데이터를 파싱할 때 사용	ARRAY[safe_offset(N)]	N+1 번째 ARRAY 속 데이터가 파싱됨 (해당 인덱스에 해당하는 값이 없다면 null 출력)
[ safe_ordinal() ]	ARRAY 데이터 중 N 번째 데이터를 파싱할 때 사용	ARRAY[safe_ordinal(N)]	N 번째 ARRAY 속 데이터가 파싱됨 (해당 인덱스에 해당하는 값이 없다면 null 출력)

위와 같이 3가지의 방법이 존재하고 그저 문법의 차이일 뿐이라 원하는 방식으로 선택해서 사용하면 될 것 같습니다.

 

개인적으로 선호하는 방법은 safe_{}로 이루어진 방식을 사용하는 것인데요.

데이터를 접하다 보면 ARRAY의 길이가 행별로 다른 경우를 마주치게 되는데, 이 경우엔 어떤 row의 경우엔 N 번째 값이 있고, 어떤 row 에는 N 번째 값이 없는 경우가 존재합니다.

 

그럴 때 [ ] 방식을 사용하면 N 번째 값이 없다는 오류메시지가 출력되는데요.

safe_{} 방식을 사용하면 그럴 경우 오류가 아닌 null 이 출력되기 때문에 이 방법을 선호하는 편입니다.

 

이해를 돕기 위한 쿼리 예시는 아래와 같습니다.

          /* 2. Array 단순 파싱 */

with json_test_tb as (
  
  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

)

select string(json_query(json_sample, '$.name')) as name
  , json_query(json_sample, '$.birth.day') as day
  , json_query(json_sample, '$.birth.month') as month
  , json_query(json_sample, '$.birth.year') as year
  , json_query_array(json_sample, '$.recent_purchase') as prd_list
  , json_query_array(json_sample, '$.recent_purchase')[0] as index_0 -- 1번째
  , json_query_array(json_sample, '$.recent_purchase')[safe_offset(0)] as offset_0 -- 1번째
  , json_query_array(json_sample, '$.recent_purchase')[safe_ordinal(1)] as ordinal_1 -- 1번째

from json_test_tb
;

ARRAY 형식 파싱하기

위 쿼리에서 index_0, offset_0, ordinal_1 컬럼을 위주로 살펴봐주시면 이해가 쉬울 거라 생각이 됩니다.

그런데 위 결과는 ARRAY의 값들을 각각의 컬럼으로 만들어주는 방법인데, 데이터를 다루다 보면 ARRAY 의 값들을 각각의 행으로 만들어줘야 하는 경우가 생깁니다.

그런 경우 어떻게 할 수 있을지를 이어서 살펴봅시다.

 

 

0.2. ARRAY 데이터를 각각의 행으로 만들기

ARRAY 형식의 데이터를 각각의 행으로 만들 때에는 UNNEST 함수를 이용하게 되는데요.

이러한 방식을 'ARRAY 데이터 평면화한다'라고 이야기하기도 하고 'UNNEST 시킨다'라고 이야기하기도 하는 것 같습니다.

 

단순히 ARRAY를 UNNEST 시키는 방식은 아래의 쿼리로 확인할 수 있는데요.

아래 쿼리는 우선 이해를 돕기 위해 ARRAY 형태의 데이터만 결괏값으로 추출하는 경우의 쿼리를 넣어뒀습니다.

          /* 3. Array 를 각각의 행으로 파싱하기 (Array 단독) */

with json_test_tb as (
  
  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

)

select *

from UNNEST(['{"product": "[할리 오브 스코틀랜드] 쉐기독 니트", "price": 200000}', '{"product": "[세인트제임스] 봉봉 맨투맨", "price": "121000"}']) as element
  WITH OFFSET as offset
;

ARRAY - UNNEST

위 결괏값을 살펴보면 기존의 ARRAY 형태와는 다르게 각각의 행 (= 2개의 행)에 값이 들어가 있음을 확인할 수 있습니다.

쿼리의 구조 및 의미들을 조금 살펴보면 아래와 같은데요.

• element라는 이름의 컬럼으로 ARRAY 데이터를 UNNEST를 시켰음
• WITH OFFSET 구문을 활용해서 ARRAY 안에 묶여 있었던 값들의 순서를 함께 가져옴 (그 이름은 offset 이라고 지정)

'FROM 절에 ARRAY 데이터를 UNNEST 시킨 값을 그대로 넣어주고 SELECT 절에서 불러왔다' 라고 생각을 해주시면 되고,

WITH OFFSET 은 ARRAY 안에 묶여있는 데이터들의 순서를 불러오기 위해 사용했다고 생각해주시면 됩니다.

(WITH OFFSET 은 굳이 사용할 필요가 없지만 ARRAY 에 묶여있는 데이터들의 순서가 중요할 때 사용해주면 됩니다.)

 

 

그런데 위 모양은 우리가 하려는게 맞긴 맞는데... 우리는 저 값을 원한게 아닙니다(?)

왜냐하면 그저 ARRAY 형태의 컬럼만 각각의 행으로 분리했을 뿐, 다른 컬럼들의 정보들을 가져오지 않았기 때문입니다.

 

그래서 UNNEST 한 ARRAY 데이터들 (= 각각의 열로 평면화된 데이터) 을 기존의 열과 결합시기는 과정이 필요한데요.

이 과정에서 CROSS JOIN 이라는 개념을 활용하게 됩니다.

 

 

0.3. ARRAY 데이터를 UNNEST 한 뒤, 다른 컬럼들과 함께 출력하기 (CROSS JOIN)

지금까지 위에서 밟아온 과정은 ARRAY 데이터를 UNNEST 하여 각각의 행으로 만들어주는 과정이었습니다.

그리고 지금 할 작업은 이를 다른 컬럼과 함께 출력하는 부분인데요.

 

이 과정을 진행할 때 이해를 쉽게 하기 위해 이렇게 생각을 한 번 해봅시다.

원래는 아래와 같이 하나의 데이터셋에 각각의 컬럼으로 들어있는 데이터였지만,

그렇게 생각을 하지 않고 ''ARRAY 형태로 쌓여있는 컬럼이 있는 테이블'과, 그렇지 않은 '일반적인 형태의 컬럼들이 있는 테이블'이 있다.'라고 생각을 해보는거죠.

• 1번 테이블 = 일반적인 형태의 컬럼들이 들어있는 테이블
• 2번 테이블 = ARRAY 형태로 쌓여있는 컬럼이 있는 테이블

< 1번 테이블 >

< 2번 테이블 >

그리고 우리는 UNNEST 를 이용해서 2번 테이블을 각각의 행으로 만들어 둔 상태라고 생각을 해봅시다.

ARRAY - UNNEST

 

그러면 이제 이 두 개의 테이블을 결합(JOIN)해야하는 과정이 남았는데, 아무리 살펴봐도 JOIN 을 하기 위한 Key 값이 없음을 알 수 있습니다.

그저 각각의 정보들만 들어있으니깐요.

그래서 우리는 Key 값이 없는 두 테이블을 결합하기 위해 CROSS JOIN 을 활용하게 됩니다.

 

위 과정을 쿼리로 나타내면 아래와 같은데요.

          /* 4. 각 행의 다른 열 값을 보존하면서 UNNEST = 이렇게 Array 를 평면화한 뒤에 다른 기존의 열들을 붙이는 식으로 작업 = CROSS JOIN 의 개념 */

with json_test_tb as (
  
  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

)

, json_parse_tb as ( -- 먼저 Array 형태를 JSON 에서 파싱을 해놓고 작업을 해야 함

  select string(json_query(json_sample, '$.name')) as name
  , json_query(json_sample, '$.birth.day') as day
  , json_query(json_sample, '$.birth.month') as month
  , json_query(json_sample, '$.birth.year') as year
  , json_query_array(json_sample, '$.recent_purchase') as prd_list

from json_test_tb

)

select a.* EXCEPT (prd_list) -- EXCEPT 를 이용해서 1번 테이블에서 ARRAY 를 제외해서 가져옴
  , prd_list_element
  , offset

from json_parse_tb a -- 1번 테이블 (ARRAY 를 제외한 컬럼들)
  CROSS JOIN UNNEST(a.prd_list) as prd_list_element -- 2번 테이블 (ARRAY 를 UNNEST 한 컬럼)
    WITH OFFSET as offset
;

위 쿼리를 조금 살펴보면,

우선 with 절로 묶여있는 json_parse_tb 까지는 JSON 으로 묶여있던 컬럼들을 각각의 컬럼으로 떼어낸 부분이고그 모습은 아래와 같습니다.

그래서 name, day, month, year 컬럼만 가져온 뒤 (1번 테이블), UNNEST 된 2번 테이블을 CROSS JOIN 해줍니다.

위 쿼리를 실행하면 아래와 같은 결과가 추출됩니다.

 

위 결과를 보면 1번 테이블 (ARRAY 가 없는 테이블)과 2번 테이블 (ARRAY 가 UNNEST 된 테이블)이 CROSS JOIN 되어 1번 테이블의 row 수 * 2번 테이블의 row 수 만큼의 행이 있는 데이터들이 추출되었음을 확인할 수 있습니다.

 

위 쿼리를 약식으로도 표현할 수 있는데요.

          /* 4.1. 4번의 다른 표현법 */

with json_test_tb as (
  
  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

)

, json_parse_tb as ( -- 먼저 Array 형태를 JSON 에서 파싱을 해놓고 작업을 해야 함

  select string(json_query(json_sample, '$.name')) as name
  , json_query(json_sample, '$.birth.day') as day
  , json_query(json_sample, '$.birth.month') as month
  , json_query(json_sample, '$.birth.year') as year
  , json_query_array(json_sample, '$.recent_purchase') as prd_list

from json_test_tb

)

select a.* EXCEPT (prd_list)
  , prd_list_element
  , offset

from json_parse_tb a, a.prd_list as prd_list_element -- , 가 CROSS JOIN 의 의미이고 UNNEST 는 생략가능
    WITH OFFSET as offset
;

json_parse_tb 까지는 위와 동일한 코드이고 마지막 SELECT 절의 FROM 절에서만 차이가 있습니다.

CROSS JOIN 이라는 표현을 , 로 대체하고 UNNEST 는 생략하여 표현을 한 것이죠.

 

사실 이 사실을 모른채 위 쿼리만 보게 되면 '대체 저게 뭐지...?' 싶을 수 있지만 이제는 당황하지 않을 수 있습니다!

만약 누군가가 작성한 쿼리가 저렇게 작성되어있다면 당황하지 않고 'UNNEST 된 ARRAY 데이터를 다른 컬럼과 같이 표현하기 위해서 CROSS JOIN을 했구나~' 라고 생각을 해주시면 됩니다!

 

여기까지 하면 거의 마무리 단계까지 왔다고 생각을 하는데요.

한 번만 더 들어가서 응용의 과정(?)까지 살펴봅시다. (말이 응용이지 어렵지 않아요!)

 

 

0.4. ARRAY 컬럼이 여러개라면 어떻게 각각의 row 로 만들어줄 수 있을까?

위의 예시에서는 하나의 ARRAY 컬럼이 존재했고 그 컬럼을 UNNEST 하여 각각의 row 로 만든 뒤 CROSS JOIN 을 해줬는데요.

만약에 ARRAY 형식의 컬럼이 여러개라면 어떻게 하면 될까요?

방법은 아주 단순합니다.

위 과정을 똑같이 각각의 ARRAY 컬럼에 적용해주면 되는거죠!

만약 ARRAY 컬럼이 2개라면 UNNEST, CROSS JOIN 을 2번 해주면 됩니다.

예시를 보면 아래와 같습니다.

          /* 5. Array 행이 여러개일 땐 어떻게 한 줄로 만들 수 있을까? : 단순하게 두 번의 Cross JOIN 을 해주면 됨 */

with json_test_tb as (
  
  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

)

, json_parse_tb as ( -- 먼저 Array 형태를 JSON 에서 파싱을 해놓고 작업을 해야 함

  select string(json_query(json_sample, '$.name')) as name
  , json_query(json_sample, '$.birth.day') as day
  , json_query(json_sample, '$.birth.month') as month
  , json_query(json_sample, '$.birth.year') as year
  , json_query_array(json_sample, '$.favorite_color') as favorite_color -- 1번 ARRAY 
  , json_query_array(json_sample, '$.recent_purchase') as prd_list -- 2번 ARRAY

from json_test_tb

)

select a.* EXCEPT (favorite_color, prd_list)
  , f_color
  , prd_list_ele

from json_parse_tb a, a.favorite_color as f_color, a.prd_list as prd_list_ele
;

위 쿼리를 실행시키고 나면 'ARRAY 가 없는 일반적인 컬럼들의 row수 * 첫 번째 ARRAY에 묶여있는 데이터 수 * 두 번째 ARRAY에 묶여있는 데이터 수' 만큼의 row를 가진 결과값이 출력됩니다.

 

그런데... 아직 약식 표현이 익숙하지 않은데 저렇게 적혀있으니 또 조금 어지러워집니다.

그러나 차근차근 위 쿼리의 FROM 절을 살펴보면 아래와 같이 생각할 수 있습니다.

select a.* EXCEPT (favorite_color, prd_list)
  , f_color
  , prd_list_ele

from json_parse_tb a
  CROSS JOIN UNNEST (a.favorite_color) as f_color
  CROSS JOIN UNNEST (a.prd_list) as prd_list_ele
-- from json_parse_tb a, a.favorite_color as f_color, a.prd_list as prd_list_ele
;

ARRAY 데이터에 익숙하지 않을 땐 이렇게 풀어서 작성하면서 약식 표현도 주석처리해서 같이 적어두는것도 좋은 방법이라고 생각합니다.

그러면 처음 코드를 보는 사람들도 이해가 쉬울테니깐요!

 

이렇게 해서 ARRAY 형태의 데이터를 각각의 행으로 바꿔어 표현하는 방법에 대해 살펴봤는데요.

마지막으로... 한 번만 더 나아가서 이제 JSON 형식의 데이터 안에 ARRAY 가 있을 때 어떻게 진행하면 될지에 대해 살펴봅시다.

(사실 이미 다 했어요)

 

 

---

1. JSON, ARRAY 가 함께 있는 경우 파싱하기

사실 지금까지 위에서 흐름으로 가져온 부분에서 추가되는 부분은 JSON 형식의 데이터에서 각각의 컬럼을 파싱해주는 부분밖에 없습니다.

(따라오다 보니 이미 모든것을 배웠달까요..?)

 

그래서 위 과정을 쿼리로 나타내면 아래와 같습니다.

 

위 쿼리의 흐름을 가지고 JSON + ARRAY 데이터를 다루는 순서를 정리해보면 아래와 같이 정리할 수 있습니다.

 

⭐️ JSON + ARRAY (+ JSON) 형태의 데이터를 다룰 때 순서 ⭐️

1) 먼저 JSON 안에 들어있는 각각의 항목들을 파싱한다.
    (그래야 ARRAY 를 UNNEST 할 수 있기 때문에, 그리고 기본적으로 JSON 형식의 데이터들을 파싱하긴 해야하니깐)

2) 파싱한 데이터 중 Array 형태가 있다면 UNNEST 이용해서 평면화하고 CROSS JOIN 을 해준다.
     (ARRAY 데이터들을 각각의 행으로 풀어서 다른 컬럼들과 함께 나타내는 과정)

3) ARRAY 안의 값들이 만약 JSON 형식이라면 다시 그 속에 있는 데이터를 파싱해서 가져온다.
     (원리는 동일하기 때문에 침착하게 대응한다면 어렵지 않지 않을까..)

 

 

---

2. 마치며

이렇게 2개의 포스팅에 걸쳐 빅쿼리에서 JSON 데이터를 다루는 방법에 대해 알아봤습니다.

JSON 형태는 평소에는 접하기 어려운 낯선 모습의 데이터이기 때문에 어려워보일 뿐, 원리를 알고나면 어렵지 않게 활용할 수 있지 않을까? 라는 생각으로 시작한 포스팅인데요.

포스팅을 하면서도 사실 이거 좀 어렵네..? 싶은 순간이 꽤 있더라구요.

그래도 포스팅을 마무리 한 지금은 '결국 JSON 과 ARRAY 의 구조와 파싱방법만 알고있다면 그 방법의 다양한 반복 및 응용이구나' 라는 생각이 듭니다.

 

이번 포스팅이 도움이 되셨길 바라며 포스팅을 마칩니다.