[PostgreSQL] MyBatis에서 PostgreSQL JSON 데이터 처리하기

1. PostgreSQL에서 JSONB 데이터 평균 구하기

JSONB 컬럼이 포함된 테이블에서 특정 키(예: "efficiency") 값을 필터링하고 평균을 구하는 기본적인 SQL 쿼리는 다음과 같습니다.

SELECT
    key AS tag_name,
    AVG(value::NUMERIC) AS avg_value
FROM sensor_data,
    LATERAL jsonb_each_text(outputs)
WHERE
    timestamp_col BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
    AND key ILIKE '%efficiency%'
    AND (value::NUMERIC) BETWEEN 0 AND 100
GROUP BY key;

이 쿼리는 다음과 같은 방식으로 동작합니다.

• jsonb_each_text(outputs): JSONB 컬럼을 키-값 쌍으로 변환
• ILIKE '%efficiency%': 특정 문자열이 포함된 키만 필터링
• BETWEEN 0 AND 100: 값이 0~100 범위 내에 있는 경우만 포함
• AVG(value::NUMERIC): 평균값 계산

2. MyBatis에서 JSONB 조회 처리하기

위의 SQL을 MyBatis에서 활용할 경우, JSON 연산자인 ?가 MyBatis의 파라미터 바인딩 문자(?)와 충돌할 수 있습니다. 이를 방지하기 위해 jsonb_exists() 함수를 사용하는 것이 좋습니다.

XML 매퍼 설정

<select id="selectAvgEfficiency" resultType="map">
  <![CDATA[
    SELECT
        key AS tag_name,
        AVG(value::NUMERIC) AS avg_value
    FROM sensor_data,
        LATERAL jsonb_each_text(outputs)
    WHERE
        timestamp_col BETWEEN #{from} AND #{to}
        AND key ILIKE '%efficiency%'
        AND (value::NUMERIC) BETWEEN 0 AND 100
  ]]>
</select>

Java 코드에서 결과 처리

MyBatis는 기본적으로 Map<String, Object> 형태로 결과를 반환합니다. 이를 HashMap<String, Double>로 변환하려면 아래와 같이 처리하면 됩니다.

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class EfficiencyDAO {

    private SqlSession sqlSession;

    public EfficiencyDAO(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    public Map<String, Double> getAverageEfficiency(String from, String to) {
        List<Map<String, Object>> resultList = sqlSession.selectList("selectAvgEfficiency", Map.of("from", from, "to", to));

        return resultList.stream()
                .collect(Collectors.toMap(
                        row -> (String) row.get("tag_name"),
                        row -> row.get("avg_value") != null ? ((Number) row.get("avg_value")).doubleValue() : 0.0
                ));
    }
}

3. 성능 최적화 방법

1) 인덱스 최적화

쿼리 성능을 높이기 위해 적절한 인덱스를 추가할 수 있습니다.

CREATE INDEX idx_timestamp_col ON sensor_data(timestamp_col);
CREATE INDEX idx_outputs ON sensor_data USING gin(outputs jsonb_path_ops);

• timestamp_col 인덱스를 추가하여 시간 범위 검색을 최적화
• GIN 인덱스를 사용하여 JSONB 키 검색 속도를 향상

2) JSONB 키 직접 참조 (LATERAL 조인 제거)

조회할 태그가 정해져 있다면 LATERAL jsonb_each_text()를 사용하지 않고 직접 JSON 키를 추출하면 성능이 개선될 수 있습니다.

SELECT
    AVG((outputs->>'efficiency_tag1')::NUMERIC) AS avg_efficiency_tag1,
    AVG((outputs->>'efficiency_tag2')::NUMERIC) AS avg_efficiency_tag2
FROM sensor_data
WHERE
    timestamp_col BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
    AND (
        (outputs ? 'efficiency_tag1' AND (outputs->>'efficiency_tag1')::NUMERIC BETWEEN 0 AND 100)
     OR (outputs ? 'efficiency_tag2' AND (outputs->>'efficiency_tag2')::NUMERIC BETWEEN 0 AND 100)
    );

이 방법은 jsonb_each_text()를 사용하지 않기 때문에 실행 속도를 줄일 수 있습니다.

마무리

PostgreSQL에서 JSONB 데이터를 다룰 때 MyBatis와 함께 사용하려면 jsonb_each_text()를 활용하여 원하는 데이터를 추출할 수 있습니다. 하지만 성능 문제를 고려하여 인덱스를 활용하거나, 고정된 태그를 직접 참조하는 방식도 고려해야 합니다.