[PostgreSQL] JSON 포맷 데이터 집계

PostgreSQL에서 JSON 형식의 데이터를 분석하려면 jsonb 데이터를 다룰 수 있는 함수와 연산자를 사용하면 됩니다. status 컬럼에 저장된 JSON 데이터에서 각 키별로 값의 종류와 그 개수를 계산하려면 jsonb_each_text 함수와 GROUP BY를 조합하여 쿼리를 작성할 수 있습니다.

예제 테이블 구조

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

-- 데이터 삽입
INSERT INTO example_table (status) VALUES
('{"key1": "good", "key2": "critical"}'),
('{"key1": "good", "key2": "warning"}'),
('{"key1": "bad", "key2": "critical"}'),
('{"key1": "good", "key2": "critical"}');

쿼리: 각 키별로 값의 종류와 개수 계산

SELECT 
    key,
    value,
    COUNT(*) AS count
FROM (
    SELECT 
        key,
        value
    FROM example_table, 
    LATERAL jsonb_each_text(status)
) subquery
GROUP BY key, value
ORDER BY key, count DESC;

쿼리 설명

1. jsonb_each_text(status):
   • status JSONB 컬럼을 키-값 쌍으로 분리하여 반환합니다.
   • 각 행의 JSON 데이터를 "key"와 "value"로 분리합니다.
2. LATERAL:
   • 각 행마다 JSON 데이터를 분리하기 위해 사용됩니다.
3. GROUP BY key, value:
   • 키(key)와 값(value) 조합별로 그룹화합니다.
4. COUNT(*):
   • 각 키-값 조합의 빈도를 계산합니다.
5. ORDER BY key, count DESC:
   • 결과를 키별로 정렬하고, 값의 빈도가 높은 순서대로 정렬합니다.

출력 예시

예를 들어 위 데이터를 기준으로 하면 다음과 같은 결과가 나옵니다:

key	value	count
key1	good	3
key1	bad	1
key2	critical	3
key2	warning	1

참고

• JSON 데이터를 효율적으로 쿼리하려면 JSONB 타입을 사용하는 것이 좋습니다.
• 데이터가 많은 경우 쿼리 성능을 높이기 위해 인덱스(GIN 인덱스)를 추가하는 것도 고려해 보세요:
• CREATE INDEX idx_status_jsonb ON example_table USING gin (status);

No function matches the given name and argument types 에러 발생할 경우

No function matches the given name and argument types 에러는 PostgreSQL에서 호출한 함수가 존재하지 않거나, 해당 함수에 전달된 매개변수의 유형이 함수의 정의와 일치하지 않을 때 발생합니다. 이 에러는 주로 다음과 같은 이유로 발생할 수 있습니다:

1. JSON 타입 문제

jsonb_each_text 함수는 JSONB 타입에서만 작동합니다. 테이블의 status 컬럼이 JSON 타입인 경우, 이를 JSONB로 변환해야 합니다.

해결 방법:

status가 JSON 타입이라면, ::jsonb를 사용하여 변환하세요:

SELECT 
    key,
    value,
    COUNT(*) AS count
FROM (
    SELECT 
        key,
        value
    FROM example_table, 
    LATERAL jsonb_each_text(status::jsonb)
) subquery
GROUP BY key, value
ORDER BY key, count DESC;

2. JSON 대신 JSONB 함수 호출

PostgreSQL에서 jsonb_each_text는 JSONB용 함수입니다. 만약 status가 JSON 타입이라면, json_each_text를 사용해야 합니다.

해결 방법:

json 타입을 그대로 사용하려면 json_each_text를 호출하세요:

SELECT 
    key,
    value,
    COUNT(*) AS count
FROM (
    SELECT 
        key,
        value
    FROM example_table, 
    LATERAL json_each_text(status)
) subquery
GROUP BY key, value
ORDER BY key, count DESC;

3. 함수 호출 오타 또는 PostgreSQL 버전 확인

PostgreSQL 버전에 따라 특정 JSON 함수가 지원되지 않을 수 있습니다. 예를 들어:

• jsonb_each_text는 PostgreSQL 9.4 이상에서만 사용할 수 있습니다.
• PostgreSQL 버전을 확인하고 (SELECT version();) 사용 중인 버전에 맞는 JSON 함수를 사용하세요.

4. LATERAL 키워드 문제

LATERAL 키워드는 PostgreSQL 9.3 이상에서 지원됩니다. 만약 이전 버전을 사용하고 있다면 다음과 같이 변경하세요:

SELECT 
    key,
    value,
    COUNT(*) AS count
FROM (
    SELECT 
        key,
        value
    FROM example_table CROSS JOIN jsonb_each_text(status::jsonb)
) subquery
GROUP BY key, value
ORDER BY key, count DESC;

5. 테이블과 컬럼 확인

• 테이블 이름(example_table) 또는 컬럼 이름(status)이 정확한지 확인하세요.
• SQL 쿼리에서 테이블 이름과 컬럼 이름이 실제 데이터베이스와 일치하는지 다시 한 번 확인합니다.

테이블 및 컬럼 정보 확인:

\d example_table

결론

1. status가 JSON 타입이면 ::jsonb로 변환하거나 json_each_text를 사용하세요.
2. PostgreSQL 버전이 9.4 이상인지 확인하세요.
3. 테이블 및 컬럼 이름이 정확한지 확인하세요.