PostgreSQL JSON 데이터 컬럼 조회 방법

JSON 데이터는 키:값 형태의 트리 구조로 저장된다. 아무래도 조회하는 게 단일 값보다 복잡하다. PostgreSQL에서 JSON 데이터를 -> 와 ->> 기호를 이용해서 조회한다.

아래와 같이 JSON 데이터를 담은 테이블을 예시로 확인해보자.

자세히 보면 이런 형태의 json 데이터가 product_details 컬럼에 저장되어 있다.

1. 예시 테이블 생성 쿼리

CREATE TABLE product_catalog (
    id SERIAL PRIMARY KEY,
    product_details JSON NOT NULL
);

INSERT INTO product_catalog (product_details) VALUES
('{"name": "Laptop", "price": 1200, "specs": {"CPU": "i7", "RAM": "16GB", "Storage": "512GB SSD"} }'),
('{"name": "Smartphone", "price": 700, "specs": {"CPU": "Snapdragon 888", "RAM": "8GB", "Storage": "256GB"} }');

1. ->

SELECT 
    product_details->'name' AS product_name
FROM product_catalog;

->는 값을 json 타입으로 가져온다. 즉 Laptop이 아니라 “Laptop”으로 가져온다.

2. ->>

SELECT 
    product_details->>'name' AS product_name
FROM product_catalog;

반면 ->> 는 값을 텍스로 가져온다.

3. 하위로 들어가기

specs로 가면 하위에 배열이 존재한다. 내부 배열에 있는 값을 조회하려면 -> 와 ->> 를 여러 번 쓰면 된다.

가령 CPU 값을 출력하는 게 목표면

SELECT 
    product_details->'specs'->>'CPU' AS specs
FROM product_catalog;

이렇게 조회한다.

눈여겨 봐야할 것은 specs로 갈 때 텍스트 타입 결과인 ->> 이 아닌 json 타입 결과인 -> 으로 먼저 가져와야 그 다음에 CPU를 가져올 수 있다는 점이다.