Home » SQL » POSTGRES » postgres json 값 가져오기 방법으로 case 절을 리팩토링 해 봅시다.

postgres json 값 가져오기 방법으로 case 절을 리팩토링 해 봅시다.

sql basic 글에서 case when 절은 생각보다 많이, 자주 쓰인다고 했어요. 특히 코딩 테스트에서 자주 등장합니다.

그런데, 조건이 매우 많아지면 case when 절도 상당히 길어지게 됩니다. 몇 가지 방법으로 단순화를 시킬 수 있습니다.

  • select union 으로 임시 view를 생성한 것을 with 절로 정의 후 이용
  • json을 이용

이 글에서는 후자의 방법을 이용합니다.


간단한 json 예제

먼저, json은 많이 들어보셨을 겁니다. 어떤 형식인지 간단히 보기만 합시다.

[그림 1] 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 속성에 접근합니다.

이제, 예제 쿼리를 보겠습니다.

[그림 2] 예제 1번 쿼리

보면, #>> ‘{“res”, “1”, “id”}’ 요래 접근한 것을 볼 수 있어요. 이 #>> 뒤에 있는 것이 path 인데요.

이는, 위 그림의 노란색과 같이 접근한다는 의미입니다. res, 1, id 순으로 access 하면 ‘3456’이 나옵니다. 이 결과를 integer로 강제 형 변환을 시켜 주었는데요. #>> 연산이 ‘text’를 돌려주기 때문입니다. 쿼리의 결과를 보겠습니다.

[그림 3] 예제 1번 쿼리의 결과

3456이 나옵니다. 의도한 바로 수행되었음을 알 수 있습니다.


key로 값 접근하기

postgres json 의 경우, path로 접근하는 방법 말고, key로도 접근하는 방법이 있습니다.

  • -> int or text
    • 속성 값에 대응되는 값을 json object로 돌려줍니다.
    • int의 경우, 배열의 원소에 접근할 때 쓰입니다.
  • ->> int or text
    • 속성 값에 대응되는 값을 text로 돌려줍니다.
    • int의 경우, -> 와 마찬가지로, 배열의 원소에 접근할 때 쓰입니다.

예제를 볼까요? case when 절에 대한 글에서, 학점을 점수로 반환하는 예제가 있었어요. case 절에 12개나 되는 조건이 들어가 있어서 지저분 했지요. 이를 어떻게 바꾸면 좋을까요? 일단, json 부터 정의해 볼까요?

[그림 4] 예제 2번의 json

A+의 속성을 가지는 경우 4.5, A 의 속성을 가지는 경우 4.3 등으로 정의할 수 있습니다. 고로, 그림 4와 같이 정의하면 됩니다.

이제 속성 A+에 접근하기 위해, ->> 를 쓰면 됩니다. 예를 들어

  • ->> ‘A+’
  • 이 경우, 속성 ‘A+’ 에 대응되는 것은 4.5이므로, ‘4.5’가 리턴됩니다.

이제 쿼리를 작성해 보겠습니다.

[그림 5] 예제 2번 쿼리

json 형식의 문자열을 ::json으로 강제 형변환 하고 ->> grade 로 접근하고 있음을 알 수 있는데요. 이는 takes의 grade가 ‘A+’, ‘A ‘ 등을 저장하기 때문입니다. 다음에, 접근한 결과를 다시 float로 형변환 합니다.

[그림 6] 예제 2번 쿼리의 결과

쿼리의 결과는 위와 같습니다. case when을 쓰지 않고도, 같은 일을 수행함을 알 수 있습니다.

Leave a Comment

18 − 13 =