카테고리:: 테이블 함수 , 반정형 및 정형 데이터 함수 (추출)

FLATTEN¶

복합 값을 여러 행으로 평면화(분해)합니다.

FLATTEN 은 VARIANT, OBJECT 또는 ARRAY 열을 사용하고 측면 뷰(즉, FROM 절에서 선행하는 다른 테이블에 대한 상관 관계를 포함한 인라인 뷰)를 생성하는 테이블 함수입니다.

FLATTEN은 반정형 데이터를 관계형 표현으로 변환하는 데 사용할 수 있습니다.

구문¶

FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ] [ , OUTER => TRUE | FALSE ] [ , RECURSIVE => TRUE | FALSE ] [ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] ) 

Copy

인자¶

필수:

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에 의해 생성된 행 수와 일치하도록 복제됩니다.

사용법 노트¶

이 함수를 정형 타입 과 함께 사용하는 방법에 대한 자세한 내용은 정형 유형의 값과 함께 FLATTEN 함수 사용 섹션을 참조하십시오.

예¶

참고 항목: 예: FLATTEN 테이블 함수와 함께 래터럴 조인 사용 및 FLATTEN을 사용하여 WHERE 절에서 결과 필터링하기.

다음의 간단한 예제는 하나의 레코드를 평면화하는 것입니다(배열의 중간 요소가 누락되어 있음).

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1, ,77]'))) f; 

Copy

+-----+------+------+-------+-------+------+ | 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; 

Copy

+-----+-----+------+-------+-------+-----------+ | 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; 

Copy

+-----+------+------+-------+-------+-------+ | 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; 

Copy

+-----+-----+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+------| +-----+-----+------+-------+-------+------+ 

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), OUTER => TRUE)) f; 

Copy

+-----+------+------+-------+-------+------+ | 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; 

Copy

+-----+-----+------+-------+------------+--------------+ | 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; 

Copy

+-----+------+------+-------+------------+--------------+ | 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; 

Copy

+-----+-----+------+-------+------------+--------------+ | 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; 

Copy

다음 쿼리의 FROM 절에서 LATERAL FLATTEN 의 여러 인스턴스에 주목하십시오. 각 LATERAL 뷰는 이전 뷰를 기반으로 여러 수준의 배열에 있는 요소를 참조합니다.

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; 

Copy

+----------+-----------------------------------------+---------+-----------------------+ | 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" | | | | | } | | | | | ] | | | | | } | | | +----------+-----------------------------------------+---------+-----------------------+ 