[PostgreSQL] JSON 컬럼에서 Key-Value 추출 및 MyBatis 연동

PostgreSQL에서는 json 또는 jsonb 타입을 사용하여 JSON 데이터를 저장할 수 있습니다. 하지만 이를 테이블 형태로 변환하여 조회할 때는 추가적인 함수가 필요합니다. 특히 MyBatis를 활용하여 Java에서 HashMap<String, String> 형태로 데이터를 가져오려면 적절한 쿼리 작성이 중요합니다. 이번 글에서는 PostgreSQL에서 JSON 데이터를 key-value 형태로 변환하는 방법과 MyBatis를 이용해 이를 Java에서 활용하는 방법을 알아보겠습니다.

1. PostgreSQL에서 JSON 데이터를 Key-Value 형태로 변환하기

1.1 테이블 및 샘플 데이터 생성

먼저 JSON 데이터를 저장하는 status 컬럼을 포함한 테이블을 생성하고, 샘플 데이터를 삽입합니다.

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    status JSONB
);

INSERT INTO test_table (status) VALUES
    ('{"key1": "value1", "key2": "value2"}'),
    ('{"keyA": "valueA", "keyB": "valueB"}');

1.2 JSON 데이터를 Key-Value 형식으로 변환하는 SQL

jsonb_each_text() 함수를 사용하여 key와 value를 추출할 수 있습니다.

SELECT
    id,
    jsonb_key AS key,
    jsonb_value AS value
FROM test_table,
LATERAL jsonb_each_text(status) AS t(jsonb_key, jsonb_value);

실행 결과

id	key	value
1	key1	value1
1	key2	value2
2	keyA	valueA
2	keyB	valueB

jsonb_each_text()를 사용하면 JSON 값이 TEXT 타입으로 변환되므로, 불필요한 쌍따옴표(")가 제거됩니다.

2. Java MyBatis를 사용하여 Key-Value 데이터 조회하기

PostgreSQL에서 추출한 데이터를 Java에서 HashMap<String, String> 형태로 가져오려면 MyBatis를 활용할 수 있습니다.

2.1 Mapper 인터페이스 정의

public interface TestMapper {
    List<Map<String, String>> getStatusKeyValue();
}

2.2 MyBatis XML Mapper 작성 (TestMapper.xml)

http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        SELECT
            jsonb_key AS "key",
            jsonb_value AS "value"
        FROM test_table,
        LATERAL jsonb_each_text(status) AS t(jsonb_key, jsonb_value);
    

2.3 Java에서 MyBatis 호출 및 데이터 출력

@Autowired
private TestMapper testMapper;

public void fetchAndPrintJsonData() {
    List<Map<String, String>> result = testMapper.getStatusKeyValue();
    
    for (Map<String, String> row : result) {
        System.out.println("Key: " + row.get("key") + ", Value: " + row.get("value"));
    }
}

2.4 실행 결과 예시

Key: key1, Value: value1
Key: key2, Value: value2
Key: keyA, Value: valueA
Key: keyB, Value: valueB

3. 정리

• PostgreSQL의 jsonb_each_text()를 사용하면 JSON 데이터를 Key-Value 형식으로 쉽게 변환할 수 있습니다.
• MyBatis에서 resultType="map"을 사용하여 조회하면 HashMap<String, String> 형태로 데이터를 가져올 수 있습니다.
• Java에서 List<Map<String, String>> 형태로 데이터를 조회하고, 이를 활용할 수 있습니다.

이를 활용하면 JSON 데이터를 보다 쉽게 조회하고 가공할 수 있습니다.