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.dev"},{"email":"email3@apost.dev"},{"email":"email5@apost.dev"},{"email":"email6@apost.dev"},{"email":"email447559@apost.dev"},{"email":"email11@apost.dev"},{"email":"email447552@apost.dev"},{"email":"email447555@apost.dev"}]

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

string_agg(컬럼명,구분자)

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

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

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

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

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

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