- 카테고리:
테이블 함수 , 반정형 및 정형 데이터 함수 (추출)
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 | FALSEFALSE인 경우, 경로에서 액세스할 수 없거나 필드 또는 항목이 0이기 때문에 확장할 수 없는 입력 행은 전부 출력에서 완전히 생략됩니다.TRUE인 경우, 0행 확장에 대해 정확히 하나의 행이 생성됩니다(KEY, INDEX, VALUE 열에 NULL 포함).
기본값:
FALSE참고
빈 복합 요소의 0행 확장은 THIS 출력 열에 NULL을 표시하여, 존재하지 않는 복합 요소 또는 잘못된 종류의 복합 요소를 확장하려는 시도와 구별합니다.
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에 의해 생성된 행 수와 일치하도록 복제됩니다.
사용법 노트¶
정형 유형 과 함께 이 함수를 사용하는 방법에 대한 지침은 정형 유형과 함께 FLATTEN 함수 사용하기 섹션을 참조하십시오.
예¶
하나의 기록을 평면화하는 간단한 예(배열의 중간 요소가 누락됨에 유의):
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 | |-----+-----+------+-------+-------+-----------| | 1 | a | a | NULL | 1 | { | | | | | | | "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@company.com" } ] } ] }'), (98127771, '{ name: { first: "Jane", last: "Doe"}, contact: [ { business:[ { type: "phone", content:"555-1236" }, { type: "email", content:"j.doe@company.com" } ] } ] }') v; -- Note the multiple instances of LATERAL FLATTEN in the FROM clause of the following query. -- Each LATERAL view is based on the previous one to refer to elements in -- multiple levels of arrays. 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@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 12712555 | { | "email" | "j.smith@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "phone" | "555-1236" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "email" | "j.doe@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | +----------+-----------------------------------------+---------+-----------------------+