- 카테고리:
테이블 함수 , 반정형 및 정형 데이터 함수 (추출)
FLATTEN¶
복합 값을 여러 행으로 평면화(분해)합니다.
FLATTEN은 VARIANT, OBJECT 또는 ARRAY 열을 사용하고 측면 뷰(즉, 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:samp:`FALSE`인 경우, 경로에서 액세스할 수 없거나 필드 또는 항목이 0개이기 때문에 확장할 수 없는 모든 입력 행은 출력에서 완전히 생략됩니다.
TRUE인 경우, 0행 확장에 대해 정확히 하나의 행이 생성됩니다(KEY, INDEX, VALUE 열에 NULL 포함).
기본값:
FALSE참고
빈 복합 요소의 0행 확장은 NULL 출력 열에 ``THIS``을 표시하여, 존재하지 않는 복합 요소 또는 잘못된 종류의 복합 요소를 확장하려는 시도와 구별합니다.
RECURSIVE => TRUE | FALSEFALSE인 경우,PATH가 참조하는 요소만 확장됩니다.TRUE인 경우, 모든 하위 요소에 대해 재귀적으로 확장이 수행됩니다.
기본값:
FALSEMODE => 'OBJECT' | 'ARRAY' | 'BOTH'오브젝트만, 배열만, 또는 둘 모두를 평면화해야 하는지 여부를 지정합니다.
기본값:
BOTH
출력¶
반환된 행은 고정된 열 세트로 구성됩니다.
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
- SEQ:
입력 레코드와 연결된 고유한 시퀀스 번호입니다. 시퀀스는 갭이 없거나 특정 방식으로 정렬된다고 보장되지 않습니다.
- KEY:
맵 또는 오브젝트의 경우, 이 열에는 분해된 값에 대한 키가 포함됩니다.
- PATH:
평면화해야 하는 데이터 구조 내의 요소에 대한 경로입니다.
- INDEX:
배열인 경우 요소의 인덱스입니다. 그렇지 않으면 NULL입니다.
- VALUE:
평면화된 배열/오브젝트의 요소 값입니다.
- THIS:
평면화되는 요소입니다(재귀적 평면화에 유용함).
참고
FLATTEN 의 데이터 소스로서 사용된 원본(상관) 테이블의 열에도 액세스할 수 있습니다. 원본 테이블의 단일 행으로 인해 평면화 뷰에 여러 행이 생성된 경우, 이 입력 행의 값은 FLATTEN에 의해 생성된 행 수와 일치하도록 복제됩니다.
사용법 노트¶
단일 수준 배열의 경우,
TABLE(FLATTEN(...))및 ``LATERAL FLATTEN(…)``은 동일한 결과를 생성합니다. 여러 FLATTEN 호출을 연결해야 하는 중첩 데이터 구조의 경우, 각 후속 FLATTEN은 이전의 출력을 참조할 수 있도록 :doc:`LATERAL </sql-reference/constructs/join-lateral>`을 사용합니다.이 함수를 정형 타입 과 함께 사용하는 방법에 대한 자세한 내용은 정형 유형의 값과 함께 FLATTEN 함수 사용 섹션을 참조하십시오.
예¶
참고 항목: 예: FLATTEN 테이블 함수와 함께 래터럴 조인 사용 및 FLATTEN을 사용하여 WHERE 절에서 결과 필터링하기.
다음의 간단한 예제는 하나의 레코드를 평면화하는 것입니다(배열의 중간 요소가 누락되어 있음).
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:"j.smith@example.com" } ] } ] }'),
(98127771,
'{ name: { first: "Jane", last: "Doe"},
contact: [
{ business:[
{ type: "phone", content:"555-1236" },
{ type: "email", content:"j.doe@example.com" } ] } ] }') 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": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 12712555 | { | "email" | "j.smith@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "phone" | "555-1236" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "email" | "j.doe@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
+----------+-----------------------------------------+---------+-----------------------+