[PostgreSQL] Json(Jsonb) 컬럼 Query 방법 (->, ->>과 json_array_elements)

이번 포스팅에서는 PostgreSQL에서 Json(Jsonb)로 이루어진 컬럼을 Query 하는 방법을 알아봅니다.

 

Json과 Jsonb의 차이

• Json의 특징
  • 입력된 텍스트의 원본을 그대로 저장
  • Json은 데이터를 불러올때 입력된 텍스트를 파싱해야하기 때문에 시간이 오래 걸림
  • 원본을 그대로 저장하기에 의미없는 공백등을 저장
  • Json 객체 내의 key 순서가 보장되며, 중복된 key가 있는 경우도 중복되어 저장
• Jsonb의 특징
  • Jsonb는 분해된 바이너리 형식으로 저장 (데이터를 정제하여 저장)
  • Jsonb는 파싱할 필요가 없기 때문에 시간이 적게 걸림
  • 공백을 저장하지 않음
  • 객체들의 key순서를 보장하지 않으며, key를 중복해서 저장하지 않음. 입력에 중복된 key가 지정된 경우 마지막 값으로 대체
  • 객체들의 key순서를 보장해야 하는 경우가 아니라면 대체로 jsonb 형식으로 데이터를 저장
  • 이번 포스팅의 데이터는 전부 jsonb로 세팅

 

->와 ->> 을 활용한 데이터 조회

샘플데이터는 포스팅의 가장 하단에서 확인할 수 있습니다.

• ->와 ->> 는 모두 JSON 데이터의 필드를 추출하는 데 사용되지만 반환되는 결과의 데이터 타입이 다름.

1. "->"
   • JSON 객체에서 지정된 키에 해당하는 값을 JSON 형식으로 반환
   • 결과값은 JSON 또는 JSONB 타입
2. "->>"
   
   • JSON 객체에서 지정된 키에 해당하는 값을 텍스트 형식으로 반환합니다.
   • 결과값은 텍스트 타입

 

->와 -->의 사용 예시

• 조회 방법 : jsonb type의 컬럼 "->" / "-->" 조회하고자 하는 'key'

SELECT  id
        , country
        , city
        , data -> 'Population' as population_json  -- '->' 사용
        , data ->> 'Population' as population_text  -- '->>' 사용
  FROM  regions

'->' 을 사용하면 jsonb타입으로, '->>'으로 하면 text 타입으로 출력된다.

• Where 절에도 위 구문을 사용할 수 있다.

SELECT  id
        , country
        , city
        , data -> 'Population' as population_json
        , data ->> 'Population' as population_text
  FROM  regions
 WHERE  1=1
        AND data ->> 'Population' = '9733509'

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

리스트로 된 데이터가 row로 변환되었다.

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

seoul과 busan을 제외한 나머지 도시의 attration_info는 null이라 출력되지 않는다.

 

샘플데이터

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"
    ]
}');

샘플데이터

참고자료

• https://www.postgresql.org/docs/current/datatype-json.html
• https://neon.tech/docs/functions/json_array_elements