이번 포스팅에서는 PostgreSQL에서 Json(Jsonb)로 이루어진 컬럼을 Query 하는 방법을 알아봅니다.
Json과 Jsonb의 차이
- Json의 특징
- 입력된 텍스트의 원본을 그대로 저장
- Json은 데이터를 불러올때 입력된 텍스트를 파싱해야하기 때문에 시간이 오래 걸림
- 원본을 그대로 저장하기에 의미없는 공백등을 저장
- Json 객체 내의 key 순서가 보장되며, 중복된 key가 있는 경우도 중복되어 저장
- Jsonb의 특징
- Jsonb는 분해된 바이너리 형식으로 저장 (데이터를 정제하여 저장)
- Jsonb는 파싱할 필요가 없기 때문에 시간이 적게 걸림
- 공백을 저장하지 않음
- 객체들의 key순서를 보장하지 않으며, key를 중복해서 저장하지 않음. 입력에 중복된 key가 지정된 경우 마지막 값으로 대체
- 객체들의 key순서를 보장해야 하는 경우가 아니라면 대체로 jsonb 형식으로 데이터를 저장
- 이번 포스팅의 데이터는 전부 jsonb로 세팅
->와 ->> 을 활용한 데이터 조회
샘플데이터는 포스팅의 가장 하단에서 확인할 수 있습니다.
- ->와 ->> 는 모두 JSON 데이터의 필드를 추출하는 데 사용되지만 반환되는 결과의 데이터 타입이 다름.
- "->"
- JSON 객체에서 지정된 키에 해당하는 값을 JSON 형식으로 반환
- 결과값은 JSON 또는 JSONB 타입
- "->>"
- JSON 객체에서 지정된 키에 해당하는 값을 텍스트 형식으로 반환합니다.
- 결과값은 텍스트 타입
->와 -->의 사용 예시
- 조회 방법 : jsonb type의 컬럼 "->" / "-->" 조회하고자 하는 'key'
SELECT id
, country
, city
, data -> 'Population' as population_json -- '->' 사용
, data ->> 'Population' as population_text -- '->>' 사용
FROM regions
- Where 절에도 위 구문을 사용할 수 있다.
SELECT id
, country
, city
, data -> 'Population' as population_json
, data ->> 'Population' as population_text
FROM regions
WHERE 1=1
AND data ->> 'Population' = '9733509'
Json의 리스트 타입의 값 조회
- Json에 리스트 타입으로 된 데이터를 조회하는 방법
- 샘플데이터의 "Attraction" 과 "Attraction_info"컬럼
- Attration 컬럼은 단순 리스트로 되어있으며, Attraction_info은 리스트 안에 json 데이터가 포함
- '->' / '-->" 와 리스트의 순서를 활용하여 데이터 조회 가능
SELECT id
, country
, city
, data -> 'Attractions' -> 0 as attraction_name
, data -> 'Attractions_info' -> 1 -> 'Description' as attraction_description
FROM regions
- attraction_name은 각각의 리스트에서 첫번째 인자만 출력
- attraction_info는 각각의 리스트에서 두번째 json을 조회한 다음, key가 "Description"인 value를 출력
json_array_elements을 활용한 json과 리스트를 row로 변환
- json에 있는 모든 요소를 row로 변환하는 방법
- json 타입이면 json_array_elements(), jsonb 타입이면 jsonb_array_elements()을 사용
- jsonb_array_elements에 해당되지 않는 컬럼들은 복제됨.
- Self join이나 Cross join 등을 활용하여 From절에서 활용
SELECT country
, city
, a as attractions
FROM regions as r
, jsonb_array_elements(r.data -> 'Attractions') as a
Json 안에 json타입이 있는 경우도 jsonb_array_elements을 활용하여 row로 변환할 수 있음
SELECT country
, city
, b ->> 'Name' as Name
, b ->> 'Description' as Description
, b ->> 'Visitors_per_year' as Visitors_per_year
FROM regions as r
, jsonb_array_elements(r.data -> 'Attractions_info') as b
샘플데이터
CREATE TABLE regions (
id SERIAL PRIMARY KEY,
country TEXT,
city TEXT,
data JSONB
);
INSERT INTO regions (country, city, data) VALUES
('South Korea', 'Seoul', '{
"Population": 9733509,
"Area_km2": 605.21,
"Attractions": [
"Gyeongbokgung Palace",
"Namsan Seoul Tower",
"Myeongdong Shopping Street"
],
"Attractions_info": [
{
"Name": "Gyeongbokgung Palace",
"Description": "A historic palace located in northern Seoul.",
"Visitors_per_year": 2000000
},
{
"Name": "Namsan Seoul Tower",
"Description": "A landmark in central Seoul offering panoramic views of the city.",
"Visitors_per_year": 1000000
},
{
"Name": "Myeongdong Shopping Street",
"Description": "A bustling shopping district popular among tourists.",
"Visitors_per_year": 1500000
}
]
}'),
('South Korea', 'Busan', '{
"Population": 3448737,
"Area_km2": 767.35,
"Attractions": [
"Haeundae Beach",
"Gamcheon Culture Village",
"Jagalchi Fish Market"
],
"Attractions_info": [
{
"Name": "Haeundae Beach",
"Description": "A famous beach in Busan, popular for its beautiful coastline.",
"Visitors_per_year": 3000000
},
{
"Name": "Gamcheon Culture Village",
"Description": "A colorful village known for its steep streets and murals.",
"Visitors_per_year": 500000
},
{
"Name": "Jagalchi Fish Market",
"Description": "The largest seafood market in Korea, located by the seaside.",
"Visitors_per_year": 2000000
}
]
}'),
('South Korea', 'Incheon', '{
"Population": 2957026,
"Area_km2": 1062.85,
"Attractions": [
"Incheon Chinatown",
"Songdo Central Park",
"Wolmido Island"
]
}'),
('South Korea', 'Daegu', '{
"Population": 2438727,
"Area_km2": 883.54,
"Attractions": [
"Apsan Park",
"Seomun Market",
"Daegu Duryu Park"
]
}');
참고자료
728x90
반응형
'SQL & DB > PostgreSQL' 카테고리의 다른 글
[SQL] 부정 논리 연산자(not in 등) 과 Null의 관계 (2) | 2024.07.08 |
---|---|
[PostgreSQL] generate_series을 활용한 날짜/시간 더미 데이터 생성 (월단위, 일단위, 시간단위) (1) | 2024.06.30 |
[PostgreSQL] PostgreSQL의 특징과 장단점 (1) | 2023.12.08 |
[PostgreSQL/MYSQL] 날짜/시간 계산(INTERVAL타입, age함수) (DATEDIFF, TIMESTAMPDIFF) (0) | 2023.11.24 |
[PostgreSQL] CSV File를 Table에 삽입(Import)하기. (2) | 2023.03.05 |