본문 바로가기

DB·SQL

MSSQL 쿼리 결과를 하나의 문자열, 또는 JSON으로 가져오기

반응형

Microsoft SQLServer는 상용 DB인 만큼

문자열 처리와 JSON 처리를 위한 다양한 기본 함수와 제어문을 제공합니다.


MSSQL에서는 쿼리문 끝에 "for 대상포맷 path" 과 같은 출력제어 옵션을 붙여서 쿼리 결과 레코드들을 대상 포맷으로 변환할 수 있습니다.


대표적인 출력제어옵션은 다음과 같은 것이 있습니다.



- "for xml path" - 쿼리 결과를 xml 포맷으로 리턴

- "for json path" - 쿼리 결과를 json 포맷으로 리턴




먼저 쿼리 결과 컬럼 값들을 하나의 문자열로 가져오려면


다소 원시적이지만 "for xml path" 를 이용한 트릭을 이용하는 방법이 있습니다.

for xml path는 원래 결과 레코드들을 XML 포맷으로 리턴해주는 출력 제어 옵션인데


select email + ',' from member where emailyn = 'Y' and email <> '' for xml path('');


이런식으로 "for xml path" 뒤에 "('')"을 추가로 붙여주면 XML 태그들이 모두 제거되어 결과가 리턴됩니다.

여기에 컬럼 뒤에 쉼표를 더 붙여서( email + ',' ) 결과를 만들어내면

아래와 같이 하나의 문자열로 결과가 리턴됩니다.




간단한 트릭이지만 유용한 방법입니다.

다만 컬럼 뒤에 쉼표를 일괄로 붙이기 때문에 문자열 끝에 쉼표가 하나더 붙어있는 문제가 있으므로 리턴된 문자열을 처리할 때 주의해야 합니다.





출력 제어 옵션으로 json 문자열을 만드려면 "for json path" 출력 제어 옵션을 사용합니다.


select email from member where emailyn = 'Y' and email <> '' for json path;


이렇게 간단하게 쿼리문 끝에 출력 제어옵션을 붙여주면 됩니다.

많은 컬럼 갯수에도 잘 대응되며, inner join으로 중첩한 쿼리 결과에 대해서도 json 데이터를 생성해줍니다.




실제 결과 값은 아래와 같은 단일 json 문자열이 됩니다.


[{"email":"email2@apost.kr"},{"email":"email3@apost.kr"},{"email":"email5@apost.kr"},{"email":"email6@apost.kr"},{"email":"email447559@apost.kr"},{"email":"email11@apost.kr"},{"email":"email447552@apost.kr"},{"email":"email447555@apost.kr"}]






MSSQL은 쿼리 결과 컬럼들을 구분자로 구분해 하나의 문자열로 묶어주는 내장 함수가 아얘 제공됩니다.


string_agg(컬럼명,구분자)


String Aggregate(문자열 집합) 의 축약 형태이고


사용법은 아래와 같습니다.


string STRING_AGG(email,',') emails FROM member where smsyn = 'Y' and hphone <> '';


결과는 아래와 같이 구분자(,)로 구분된 하나의 문자열 레코드가 리턴됩니다.




다소 원시적이었던 앞의 for xml path('') 와 비교해 훨씬 세련되고 간결한 결과를 얻을 수 있습니다.

결과 문자열 끝에 불필요하게 구분자가 더 붙지 않기 때문에 결과도 예측 가능합니다.

반응형

닫기