[PostgreSQL] 실전 활용 - 문자열 포함, 쉼표 구분 배열 매칭, JSON 배열 필터링

업무 중 PostgreSQL을 다루면서 특정 컬럼의 값이 다른 컬럼에 포함되는 경우를 추출하거나, 쉼표(,)로 구분된 문자열 배열에서 완전 일치 여부를 확인하거나, JSON 배열 안에서 특정 값을 필터링해야 할 일이 있었다. 각각 다른 상황이지만, 공통적으로 SQL을 조금만 다듬으면 깔끔하게 해결할 수 있었다. 정리해보면 다음과 같다.

1. 문자열 포함 여부 비교 (col_a 값이 col_b 안에 포함되는 경우)

예를 들어 col_a 컬럼은 'abc', col_b 컬럼은 'abcdf'처럼 저장되어 있을 때, col_a 컬럼 값이 col_b 컬럼 안에 포함되는지 비교하고 싶었다.
PostgreSQL에서는 POSITION() 함수나 STRPOS() 함수를 이용하면 쉽게 해결할 수 있었다.

SELECT *
FROM example_table
WHERE POSITION(col_a IN col_b) > 0;

또는

SELECT *
FROM example_table
WHERE STRPOS(col_b, col_a) > 0;

두 방법 모두 col_a 컬럼의 값이 col_b 컬럼 문자열 안에 포함되어 있는 경우를 추출해준다.

2. 쉼표(,)로 구분된 문자열 배열 중 완전 일치 여부 비교

추가로 col_b 컬럼이 'ad,bdd,cc'와 같이 쉼표로 구분된 여러 문자열로 저장되어 있을 때, col_a 컬럼의 값이 col_b 컬럼 안의 요소와 완전히 일치하는 경우만 추출하고 싶었다.

이때는 string_to_array() + ANY() 조합이 가장 간편했다.

SELECT *
FROM example_table
WHERE col_a = ANY(string_to_array(col_b, ','));

조금 더 유연하게 다양한 조건을 붙이고 싶을 때는 regexp_split_to_table()과 EXISTS를 사용할 수도 있다.

SELECT *
FROM example_table e
WHERE EXISTS (
    SELECT 1
    FROM regexp_split_to_table(col_b, ',') AS token
    WHERE token = e.col_a
);

이 방법은 확장성이 좋고, 실전에서도 여러 조건을 함께 적용할 수 있어서 유용했다.

3. JSON 배열 안에서 특정 값('None') 제외하고 추출

또 다른 케이스로, col_json이라는 컬럼에
{"keys": ["a", "b", "None", "c"]}
처럼 JSON 데이터가 저장되어 있을 때, 'None'이라는 값을 제외하고 나머지만 추출해야 했다.

처음에는 jsonb_array_elements_text()를 단독으로 썼지만, 더 안전하고 깔끔하게 하려면 LATERAL을 이용하는 게 좋았다.

SELECT t.id, elem
FROM example_table t,
LATERAL jsonb_array_elements_text(t.col_json::jsonb -> 'keys') AS elem
WHERE elem <> 'None';

LATERAL을 사용하면 각 row 별로 JSON 배열을 펼치면서 동시에 원하는 조건으로 필터링할 수 있어서, 성능과 안정성 모두 챙길 수 있었다.

마무리

이번 경험을 통해 PostgreSQL에서는 데이터 타입과 저장 형태에 따라 다양한 문자열/배열/JSON 조작이 가능하다는 것을 다시 느꼈다.

• 단순 포함 비교는 POSITION, STRPOS
• 쉼표 구분 배열은 string_to_array, regexp_split_to_table
• JSON 배열은 jsonb_array_elements_text, LATERAL
  을 상황에 맞게 조합해서 쓰는 것이 핵심이었다.