언네스팅

언네스팅 (Unnesting)

중첩된 데이터를 펼쳐서 다루고 싶을 때가 많아요. 언네스팅(unnesting)은 복합 타입(composite types)의 값을 그 구성 요소들로 분해하는 연산이에요. LIST와 STRUCT 타입의 값은 unnest() 함수를 사용해 언네스트할 수 있어요.

  • 언네스팅은 LIST 타입의 값을 테이블 열로 바꿔요: 각 리스트 요소가 행을 만들고, 각 요소는 열 값이 돼요.
  • STRUCT 타입의 값을 언네스트하면 각 멤버에 대해 열이 만들어져요. 멤버 키(key)가 열 이름이 되고, 멤버 값이 열 값이 돼요. STRUCT 타입의 값은 점-별(⟨struct⟩.*) 축약 문법을 사용해서도 언네스트할 수 있지만, unnest() 함수는 몇 가지 추가 기능을 제공해요.
  • 이름 없는 구조체(unnamed struct)의 경우, 언네스팅 연산은 이름 있는 STRUCT와 동일하지만, 이 경우 열 이름은 멤버의 (1부터 시작하는) 서수 위치를 기반으로 만들어지며 element 접두사가 붙어요. 점-별(⟨struct⟩.*) 축약 문법은 이름 없는 구조체에는 사용할 수 없어요: 이름 없는 구조체는 unnest() 함수로만 언네스트할 수 있어요.

출처: DuckDB 공식 문서 — Unnesting

unnest() 호출하기

언네스트할 LIST 또는 STRUCT 값은 항상 unnest() 함수의 첫 번째 — 필수 — 인자로 전달돼요. unnest() 함수는 재귀 언네스팅의 동작을 제어하기 위한 몇 가지 선택적 추가 인자를 가져요.

unnest 함수는 스칼라 함수인 것처럼 SELECT 절에서 호출할 수 있어요. unnest 함수는 WHERE, GROUP BY, ORDER BY 절처럼 일반적으로 스칼라 함수를 호출할 수 있는 다른 맥락에서는 호출할 수 없어요. LIST 타입의 값에 적용될 때 unnest()는 일반적으로 테이블 함수를 사용할 수 있는 절에서도 나타날 수 있어요.

LIST 타입 값 언네스팅

LIST 타입 값을 언네스팅하는 것을 완전히 이해하려면, 언네스트할 LIST 타입 값을 제공한 '입력(input)' 행과 언네스팅 연산이 만든 '출력(output)' 행을 구분하는 것이 유용해요. LIST 타입 값에 대한 unnest()의 개별 호출은 unnest() 호출에 대응하는 필드를 요소 값으로 채우면서 '입력' 행을 복제하는 효과가 있어요. 다시 말해, 원래 행은 LIST 타입 값에서 언네스트된 요소들의 반복 그룹(repeating group)이 돼요. 결과적으로, unnest()가 빈 리스트(또는 NULL 값)에 호출되면 요소가 언네스트되지 않고 '출력' 행도 생성되지 않아요.

여러 리스트 언네스팅

여러 LIST 타입 값을 같은 SELECT 절 내에서 언네스트할 수 있어요. 그래서 하나의 '입력' 행에 대해 unnest() 호출로 인한 여러 행 집합이 있을 수 있고, 각각은 자체적인 행 수를 가질 수 있어요. 각 결과는 출력 테이블의 열이 되며, 서수 위치로 값을 정렬하고, 특정 결과가 다른 결과보다 요소 수가 적으면 NULL 값으로 열을 채워 넣어요. 마지막 단계에서 입력 행의 열이 이 결과에 추가돼요. 다시 말해, 반복 그룹은 각 개별 unnest() 결과에 대해 다시 만들어지는 것이 아니라, 모든 unnest() 결과에 대해 한 번만 만들어져요.

요소 인덱스 얻기

LIST 타입의 값을 언네스트하면 요소 값만 산출돼요. 그들의 인덱스(첨자)를 추적하려면 내장 매크로 generate_subscripts()를 사용할 수 있어요. generate_subscripts 매크로는 첫 번째 인자로 LIST 타입의 값을 받아요.

unnest()를 테이블 함수로

unnest 호출이 LIST 타입의 값에 대해 행 집합을 산출하므로, 테이블 함수(table function)로 취급될 수도 있어요. 이는 FROM 절이나 CALL 문에 나타날 수 있다는 뜻이에요.

FROM 절이나 CALL 문에서의 unnest() 호출은 추가 매개변수를 받지 않으므로, 재귀 언네스팅에는 사용할 수 없어요.

재귀 언네스팅 (Recursive Unnesting)

기본적으로 unnest()는 복합 타입 값의 가장 바깥쪽 구성 요소만 풀어내요. 언네스트된 멤버 값과 그 값들의 언네스트된 값 등에 재귀적으로 언네스팅 연산을 적용할 수 있도록 추가 매개변수를 전달할 수 있어요. 이러한 추가 매개변수는 다음과 같아요:

  • recursive: BOOLEAN, 기본값: false. true를 전달하면 언네스트된 멤버 값에 unnest를 재귀적으로 계속 적용해요. false를 명시적으로 전달하면 재귀가 비활성화돼요. 이 경우 max_depth와 keep_parent_names 같은 다른 추가 매개변수는 실질적으로 무시돼요.
  • max_depth: UINT32, 기본값: 1. 재귀가 최대 몇 단계까지 적용될지 제어해요. 1보다 큰 값은 재귀를 의미하며, 이 경우 recursive를 명시적으로 true로 전달할 필요가 없어요.
  • keep_parent_names: BOOLEAN, 기본값: false. 모든 조상 멤버의 키를 사용해 열 이름을 만들지 여부예요. 이 인자는 STRUCT 값을 언네스트할 때만 적용돼요.

재귀 언네스팅은 항상 가장 바깥쪽 unnest() 호출의 타입을 존중한다는 점을 참고하세요:

  • LIST 타입 값이 전달되면 LIST 타입 요소는 재귀적으로 언네스트되지만, STRUCT 타입 요소는 더 이상 풀리지 않아요.
  • STRUCT 타입 값이 전달되면 STRUCT 타입 멤버 값은 재귀적으로 언네스트되지만, LIST 타입 멤버 값은 더 이상 풀리지 않아요.

예제 (Examples)

리스트를 언네스트하여 3개의 행(1, 2, 3)을 생성해요:

SELECT unnest([1, 2, 3]);

구조체를 언네스트하여 두 개의 열(a, b)을 생성해요:

SELECT unnest({'a': 42, 'b': 84});

구조체 리스트를 재귀 언네스트해요:

SELECT unnest([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);

max_depth를 사용해 재귀 언네스트 깊이를 제한해요:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 2);

리스트 언네스팅

리스트를 언네스트하여 3개의 행(1, 2, 3)을 생성해요:

SELECT unnest([1, 2, 3]);

리스트를 언네스트하여 3개의 행((1, 10), (2, 10), (3, 10))을 생성해요:

SELECT unnest([1, 2, 3]), 10;

서로 다른 크기의 두 리스트를 언네스트하여 3개의 행((1, 10), (2, 11), (3, NULL))을 생성해요:

SELECT unnest([1, 2, 3]), unnest([10, 11]);

서브쿼리에서 리스트 열을 언네스트해요:

SELECT unnest(l) + 10 FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);

빈 결과:

SELECT unnest([]);

빈 결과:

SELECT unnest(NULL);

리스트에 unnest를 사용하면 리스트 항목마다 하나의 행을 방출해요. 같은 SELECT 절의 일반적인 스칼라 표현식은 방출되는 모든 행에 대해 반복돼요. 같은 SELECT 절에서 여러 리스트가 언네스트되면 리스트는 나란히 언네스트돼요. 한 리스트가 다른 리스트보다 길면, 짧은 리스트는 NULL 값으로 채워져요.

빈 리스트와 NULL 리스트는 모두 0개의 행으로 언네스트돼요.

구조체 언네스팅

구조체를 언네스트하여 두 개의 열(a, b)을 생성해요:

SELECT unnest({'a': 42, 'b': 84});

구조체를 언네스트하여 두 개의 열(a, b)을 생성해요:

SELECT unnest({'a': 42, 'b': {'x': 84}});

구조체에 대한 unnest는 구조체의 항목마다 하나의 열을 방출해요.

재귀 언네스트

리스트의 리스트를 재귀적으로 언네스트하여 5개의 행(1, 2, 3, 4, 5)을 생성해요:

SELECT unnest([[1, 2, 3], [4, 5]], recursive := true);

구조체 리스트를 재귀적으로 언네스트하여 두 열(a, b)의 두 행을 생성해요:

SELECT unnest([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);

구조체를 언네스트하여 두 개의 열(a, b)을 생성해요:

SELECT unnest({'a': [1, 2, 3], 'b': 88}, recursive := true);

recursive 설정으로 unnest를 호출하면 리스트를 완전히 언네스트한 다음 구조체를 완전히 언네스트해요. 이는 리스트 안의 리스트나 구조체 리스트를 포함하는 열을 완전히 평평하게 만드는 데 유용할 수 있어요. 구조체 안의 리스트는 언네스트되지 않는다는 점을 참고하세요.

언네스팅 최대 깊이 설정

max_depth 매개변수는 재귀 언네스팅의 최대 깊이를 제한할 수 있게 해줘요 (기본적으로 가정되며 별도로 지정할 필요는 없어요). 예를 들어 max_depth 2로 언네스트하면:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 2) AS x;
x
[1, 2]
[3, 4]
[5, 6]
[7, 8, 9]
[]
[10, 11]

한편, max_depth 3으로 언네스트하면:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 3) AS x;
x
1
2
3
4
5
6
7
8
9
10
11

리스트 항목 위치 추적하기

각 항목의 원래 리스트 내 위치를 추적하려면 unnest를 generate_subscripts와 결합할 수 있어요:

SELECT unnest(l) AS x, generate_subscripts(l, 1) AS index
FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);
x index
1 1
2 2
3 3
4 1
5 2

재귀 언네스팅 시 열 이름 유지하기

keep_parent_names 매개변수는 이름 있는 구조체를 재귀적으로 언네스트할 때 부모 열 이름을 유지하는 데 사용할 수 있어요. 예를 들어 keep_parent_names를 활성화한 다음 쿼리를 언네스트하면:

SELECT unnest([{'a': 0, 'b': {'bb': {'bbb': 1}}}], recursive := true, keep_parent_names := true);

다음과 같은 결과가 산출돼요:

a b.bb.bbb
0 1

이 경우 필드 이름이 보존되어 가장 안쪽 값까지의 경로를 보여줘요. 이는 복잡한 중첩 데이터 구조를 다룰 때 특히 유용한데, 원본 데이터의 구조와 명명 규칙을 유지하기 때문이에요. 이 매개변수는 max_depth 매개변수와 함께 사용할 수도 있어, 더 세밀한 제어가 가능하고 중첩 구조를 더 정밀하게 관리할 수 있어요.

더 알아보기 (Learn more)