FLATTEN
FLATTEN
복합 값을 여러 행으로 평탄화(폭발, explode)해요.
FLATTEN은 VARIANT, OBJECT 또는 ARRAY 열을 받아 래터럴 뷰(lateral view)를 만드는 테이블 함수예요. 래터럴 뷰는 FROM 절에서 앞에 오는 다른 테이블과의 상관 관계를 포함하는 인라인 뷰예요.
FLATTEN은 반정형 데이터를 관계형 표현으로 변환하는 데 사용할 수 있어요.
본문
구문
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
[ , OUTER => TRUE | FALSE ]
[ , RECURSIVE => TRUE | FALSE ]
[ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
인자
필수:
INPUT => expr
- 행으로 평탄화될 표현식이에요. 표현식은 VARIANT, OBJECT 또는 ARRAY 데이터 타입이어야 해요.
선택:
PATH => constant_expr
- 평탄화해야 할 VARIANT 데이터 구조 안의 요소에 대한 경로예요. 가장 바깥쪽 요소를 평탄화하려면 길이 0인 문자열(즉, 빈 경로)일 수 있어요. 기본값: 길이 0인 문자열(빈 경로)
OUTER => TRUE | FALSE
- FALSE이면 경로에서 접근할 수 없거나 필드·항목이 0개여서 확장할 수 없는 입력 행은 출력에서 완전히 생략돼요. TRUE이면 0개 행 확장에 대해 정확히 한 행이 생성돼요(KEY, INDEX, VALUE 열에 NULL). 기본값: FALSE. 빈 복합 값의 0개 행 확장은 THIS 출력 열에 NULL을 표시해, 존재하지 않거나 잘못된 종류의 복합 값을 확장하려는 시도와 구분돼요.
RECURSIVE => TRUE | FALSE
- FALSE이면 PATH가 참조하는 요소만 확장돼요. TRUE이면 모든 하위 요소에 대해 재귀적으로 확장이 수행돼요. 기본값: FALSE
MODE => 'OBJECT' | 'ARRAY' | 'BOTH'
- 객체만, 배열만, 또는 둘 다 평탄화할지 지정해요. 기본값: BOTH
출력
반환되는 행은 고정된 열 집합으로 구성돼요.
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
SEQ:
- 입력 레코드와 연관된 고유 시퀀스 번호예요. 시퀀스는 빈틈이 없거나 특정 순서로 정렬된다는 보장이 없어요.
KEY:
- 맵 또는 객체의 경우 이 열은 폭발된 값에 대한 키를 담아요.
PATH:
- 평탄화해야 할 데이터 구조 안의 요소에 대한 경로예요.
INDEX:
- 요소가 배열이면 요소의 인덱스이고, 그렇지 않으면 NULL이에요.
VALUE:
- 평탄화된 배열/객체 요소의 값이에요.
THIS:
- 평탄화되고 있는 요소예요(재귀 평탄화에서 유용).
참고: FLATTEN의 데이터 소스로 사용된 원래(상관) 테이블의 열도 접근할 수 있어요. 원래 테이블의 단일 행이 평탄화된 뷰에서 여러 행이 되면, 이 입력 행의 값들은 FLATTEN이 생성한 행 수에 맞게 복제돼요.
사용 시 참고 사항
- 단일 수준 배열의 경우 TABLE(FLATTEN(...))과 LATERAL FLATTEN(...)은 같은 결과를 만들어요. 여러 FLATTEN 호출을 연결해야 하는 중첩 데이터 구조에서는 LATERAL을 사용해 각 후속 FLATTEN이 이전 FLATTEN의 출력을 참조할 수 있게 하세요.
- 구조화(structured) 타입과 함께 이 함수를 사용하는 방법은 Using the FLATTEN function with values of structured types 문서를 참고하세요.
예시
Example: Using a lateral join with the FLATTEN table function과 Using FLATTEN to Filter the Results in a WHERE Clause 문서도 참고하세요.
다음 간단한 예시는 레코드 하나를 평탄화해요(배열의 중간 요소가 비어 있음에 유의).
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1, ,77]'))) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | [0] | 0 | 1 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
| 1 | NULL | [2] | 2 | 77 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
+-----+------+------+-------+-------+------+
다음 두 쿼리는 PATH 매개변수의 효과를 보여줘요.
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), OUTER => TRUE)) f;
+-----+-----+------+-------+-------+-----------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+-----------|
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
+-----+-----+------+-------+-------+-----------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), PATH => 'b')) f;
+-----+------+------+-------+-------+-------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+-------|
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
+-----+------+------+-------+-------+-------+
다음 두 쿼리는 OUTER 매개변수의 효과를 보여줘요.
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'))) f;
+-----+-----+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+------|
+-----+-----+------+-------+-------+------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), OUTER => TRUE)) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | | NULL | NULL | [] |
+-----+------+------+-------+-------+------+
다음 두 쿼리는 RECURSIVE 매개변수의 효과를 보여줘요.
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE )) f;
+-----+------+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+------+------+-------+------------+--------------+
다음 예시는 MODE 매개변수의 효과를 보여줘요.
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE, MODE => 'OBJECT' )) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
다음 예시는 배열 안에 중첩된 배열을 폭발시켜요. 다음 테이블을 만듭니다.
CREATE OR REPLACE TABLE persons AS
SELECT column1 AS id, PARSE_JSON(column2) as c
FROM values
(12712555,
'{ name: { first: "John", last: "Smith"},
contact: [
{ business:[
{ type: "phone", content:"555-1234" },
{ type: "email", content:"[email protected]" } ] } ] }'),
(98127771,
'{ name: { first: "Jane", last: "Doe"},
contact: [
{ business:[
{ type: "phone", content:"555-1236" },
{ type: "email", content:"[email protected]" } ] } ] }') v;
다음 쿼리는 여러 LATERAL FLATTEN 호출을 사용해요. 두 번째 FLATTEN이 첫 번째의 출력(f.value:business)을 참조하므로 여기서는 LATERAL이 필요해요. LATERAL이 없으면 두 번째 FLATTEN이 첫 번째 호출의 열에 접근할 수 없어요.
SELECT id as "ID",
f.value AS "Contact",
f1.value:type AS "Type",
f1.value:content AS "Details"
FROM persons p,
LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
LATERAL FLATTEN(INPUT => f.value:business) f1;
+----------+-----------------------------------------+---------+-----------------------+
| ID | Contact | Type | Details |
|----------+-----------------------------------------+---------+-----------------------|
| 12712555 | { | "phone" | "555-1234" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "[email protected]", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 12712555 | { | "email" | "[email protected]" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "[email protected]", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "phone" | "555-1236" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "[email protected]", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "email" | "[email protected]" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "[email protected]", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
+----------+-----------------------------------------+---------+-----------------------+
더 알아보기
- Table functions — 테이블 함수 모음
- Semi-structured and structured data functions — 반정형·구조적 데이터 함수 모음
- LATERAL — 래터럴 조인
- Using FLATTEN with structured types — 구조화 타입 값과 함께 FLATTEN 사용