배열에서 데이터 뽑기: unnest(), array_length(), array_position()

배열은 그냥 만들고 저장하는 걸로 끝나는 게 아니야 — 종종 배열에서 특정 요소를 뽑거나 내용을 분석해야 할 때가 많지. PostgreSQL에는 이런 작업을 위한 내장 함수들이 있어. 하나씩 살펴보자.

unnest() 함수: 배열 풀기

unnest() 함수는 말 그대로 배열을 풀어서 각 요소를 개별 행으로 만들어줘. 배열 데이터를 테이블처럼 다뤄야 할 때 진짜 유용해.

예시 1: 배열 간단히 풀기

예를 들어, 학과 이름이 들어있는 배열이 있다고 해보자:

SELECT ARRAY['컴퓨터과학', '수학', '물리'] AS 학과들;

이제 각 요소를 한 줄씩 뽑고 싶으면 unnest()를 써봐:

SELECT unnest(ARRAY['컴퓨터과학', '수학', '물리']) AS 학과;

결과:

예시 2: 테이블에서 배열 풀기

이번엔 courses라는 테이블이 있다고 해보자:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT,
    tags TEXT[]
);

INSERT INTO courses (course_name, tags)
VALUES
    ('알고리즘', ARRAY['프로그래밍', '컴퓨터과학']),
    ('선형대수', ARRAY['수학', '대수']),
    ('기초물리', ARRAY['물리', '일반']);

이제 배열에서 모든 태그를 뽑아보자:

SELECT course_name, unnest(tags) AS tag
FROM courses;

결과:

보다시피, 배열의 각 요소가 테이블의 한 줄로 바뀌었지.

array_length() 함수: 배열 크기 알아내기

배열 다룰 때 두 번째로 중요한 함수는 array_length()야. 이 함수는 지정한 차원의 배열 길이(즉, 요소 개수)를 반환해줘.

예시 1: 1차원 배열 요소 개수

이런 배열이 있다고 해보자:

SELECT ARRAY['사과', '바나나', '오렌지'] AS 과일들;

과일이 몇 개인지 알고 싶으면:

SELECT array_length(ARRAY['사과', '바나나', '오렌지'], 1) AS 길이;

결과:

여기서 1은 배열의 차원을 의미해. PostgreSQL 배열은 다차원(예: 2차원 배열)도 가능한데, 그건 나중에 따로 다뤄볼게.

예시 2: 테이블 배열 요소 개수

각 강의마다 태그가 몇 개인지 볼까?

SELECT course_name, array_length(tags, 1) AS 태그_개수
FROM courses;

결과:

이 함수는 그냥 "여기 두 개 있어!"라고 알려주니까, 데이터 분석할 때 진짜 편해.

array_position() 함수: 배열에서 값 찾기

이번엔 배열에서 특정 값을 찾고 싶을 때를 생각해보자. array_position() 함수가 딱이야: 이 함수는 해당 값이 처음 나오는 위치(인덱스)를 반환해줘.

예시 1: 요소 찾기

이런 배열이 있다고 해보자:

SELECT ARRAY['빨강', '파랑', '초록', '노랑'] AS 색상들;

"파랑"의 위치를 찾아볼까:

SELECT array_position(ARRAY['빨강', '파랑', '초록', '노랑'], '파랑') AS 위치;

결과:

만약 요소가 없으면 함수는 NULL을 반환해. 확인해보자:

SELECT array_position(ARRAY['빨강', '파랑', '초록', '노랑'], '검정') AS 위치;

결과:

예시 2: 테이블 배열에서 찾기

어떤 강의에 "컴퓨터과학" 태그가 있는지 알고 싶으면 이렇게 해봐:

SELECT course_name, array_position(tags, '컴퓨터과학') AS 위치
FROM courses;

결과:

이제 태그가 있는 행만 남기고 싶으면 이렇게 필터링해:

SELECT course_name
FROM courses
WHERE array_position(tags, '컴퓨터과학') IS NOT NULL;

결과:

array_position() 함수 덕분에 배열 안에서 원하는 데이터를 빠르게 찾을 수 있어서, 배열 다룰 때 거의 필수야.

함수들 실전 활용법

• unnest() — 배열을 행으로 바꿀 때 써. 데이터 분석, 리포트 만들기, 태그 작업할 때 필수야.
• array_length() — 배열 길이 체크할 때 딱이야. 예를 들어, 데이터 검증할 때 배열이 비었는지 확인할 수 있어.
• array_position() — 요소 찾기에 최고. 예를 들어, 상품 카테고리, 학생 프로젝트 참여, 설명에 키워드 있는지 등등.

함수 쓸 때 흔히 하는 실수

1. unnest()를 여러 컬럼 배열에 동시에 쓰면 행이 두 배로 늘어날 수 있어. JOIN LATERAL이나 CROSS JOIN으로 해결할 수 있는데, 조심해야 해.
2. array_length()는 빈 배열이면 NULL을 반환해. 배열이 비었을 수도 있으면 따로 체크해줘야 해.
3. array_position()은 요소가 없으면 NULL을 반환해. 그래서 필터 쓸 때 항상 IS NOT NULL을 같이 써주는 게 좋아.

실전에서 자주 쓰는 예시

PostgreSQL 배열은 그냥 이론이 아니라, 실제 프로젝트에서 진짜 많이 써. 예를 들어, 블로그를 운영하는데 각 글마다 태그 리스트가 있다면 — 배열로 저장하면 원하는 주제별로 글을 쉽게 필터링하거나 인기 카테고리 순위도 만들 수 있어.

또는, 여러 코스나 상품을 고르는 사용자 행동을 분석할 때도 배열이 편해 — 선호도를 배열에 저장하고, 똑같이 쉽게 처리할 수 있지.

그리고 배열은 데이터 검증에도 좋아: array_length()로 요소 개수를 제한해서, 예를 들어 사용자가 다섯 개 이상 선택 못 하게 할 수도 있어.