sql basic 글에서 case when 절은 생각보다 많이, 자주 쓰인다고 했어요. 특히 코딩 테스트에서 자주 등장합니다.
그런데, 조건이 매우 많아지면 case when 절도 상당히 길어지게 됩니다. 몇 가지 방법으로 단순화를 시킬 수 있습니다.
- select union 으로 임시 view를 생성한 것을 with 절로 정의 후 이용
- json을 이용
이 글에서는 후자의 방법을 이용합니다.
간단한 json 예제
먼저, json은 많이 들어보셨을 겁니다. 어떤 형식인지 간단히 보기만 합시다.
json 예제 파일을 볼게요. 먼저, {} 안에 있는 것들은 object라고 부를 거에요. 객체 안에는 무엇이 있나요?
- 속성
- 속성에 대한 값
이 2개가 있어요. “next”, “res” 가 gh.json에 있는데요. 이를 무엇이라고 부를 것이냐? 속성이라고 부를 거에요. 그에 대응되는 값을 속성에 대한 값이라고 부를게요. 예를 들어, 속성 “next”의 값은 -1인 것입니다.
다음, object가 여러 개 모일 수 있어요. 여러 개 모여 있는 집합을 배열이라 부를게요. 이 배열은 [] 안에 있어요. “next” 속성에 대한 값은 배열인데요. 이 배열에 여러 개의 다른 object들이 모여 있어요. 여기까지 정리해 봅시다.
이 문서에서는 object의 속성 값을 key라고 합니다. 그리고 속성 값에 대응되는 것을 value라고 합니다. 여기서 질문. 그러면, 속성 next의 값은 무엇일까요? -1입니다. res의 값은 object의 배열입니다.
그러면 path는 무엇일까요? 예를 들어, res 속성의 1번째 item의 속성 id 값인 3에 접근해야 한다고 해 봅시다. 위 그림에서는, res, 1, id 순으로 접근해야 3을 얻을 수 있어요. 이를 경로로 표현하면, res – 1 – id가 됩니다. 이를 path 라고 합니다. 속성에 접근하는 방법을 의미해요. 여기까지 이해하셨다면 예제로 넘어가겠습니다.
postgres json path 로 값 접근하기
먼저 path 로 값에 접근하는 방법을 알아보겠습니다.
- #> text[]
- json object 를 돌려줍니다.
- #>> text[]
- text 를 돌려줍니다.
이렇게 두 가지 방법이 있어요. 여기서 #> 나, #>> 뒤에 오는 배열에는 path를 넘겨줍니다. 예를 들어 res 속성의 1번째 item의 속성 id에 접근해야 한다고 합시다. 이 경우, text [] 에는 아래와 같이 들어갑니다.
{“res”, “1”, “id”}
text “res”, “1”, “id”가 있는 text array입니다.
path가 “res” – “1” – “id”로 잡히므로, res 속성의 1번째 item의 id 속성에 접근합니다.
이제, 예제 쿼리를 보겠습니다.
보면, #>> ‘{“res”, “1”, “id”}’ 요래 접근한 것을 볼 수 있어요. 이 #>> 뒤에 있는 것이 path 인데요.
이는, 위 그림의 노란색과 같이 접근한다는 의미입니다. res, 1, id 순으로 access 하면 ‘3456’이 나옵니다. 이 결과를 integer로 강제 형 변환을 시켜 주었는데요. #>> 연산이 ‘text’를 돌려주기 때문입니다. 쿼리의 결과를 보겠습니다.
3456이 나옵니다. 의도한 바로 수행되었음을 알 수 있습니다.
key로 값 접근하기
postgres json 의 경우, path로 접근하는 방법 말고, key로도 접근하는 방법이 있습니다.
- -> int or text
- 속성 값에 대응되는 값을 json object로 돌려줍니다.
- int의 경우, 배열의 원소에 접근할 때 쓰입니다.
- ->> int or text
- 속성 값에 대응되는 값을 text로 돌려줍니다.
- int의 경우, -> 와 마찬가지로, 배열의 원소에 접근할 때 쓰입니다.
예제를 볼까요? case when 절에 대한 글에서, 학점을 점수로 반환하는 예제가 있었어요. case 절에 12개나 되는 조건이 들어가 있어서 지저분 했지요. 이를 어떻게 바꾸면 좋을까요? 일단, json 부터 정의해 볼까요?
A+의 속성을 가지는 경우 4.5, A 의 속성을 가지는 경우 4.3 등으로 정의할 수 있습니다. 고로, 그림 4와 같이 정의하면 됩니다.
이제 속성 A+에 접근하기 위해, ->> 를 쓰면 됩니다. 예를 들어
- ->> ‘A+’
- 이 경우, 속성 ‘A+’ 에 대응되는 것은 4.5이므로, ‘4.5’가 리턴됩니다.
이제 쿼리를 작성해 보겠습니다.
json 형식의 문자열을 ::json으로 강제 형변환 하고 ->> grade 로 접근하고 있음을 알 수 있는데요. 이는 takes의 grade가 ‘A+’, ‘A ‘ 등을 저장하기 때문입니다. 다음에, 접근한 결과를 다시 float로 형변환 합니다.
쿼리의 결과는 위와 같습니다. case when을 쓰지 않고도, 같은 일을 수행함을 알 수 있습니다.