MS SQL Server 의 FOR JSON AUTO 로 쿼리결과값을 JSON 문자열로 생성하기

SQL Server 에서는 FOR JSON쿼리 에서 절을 사용하여 결과를 JSON으로 형식화 할 수 있습니다 . 이 작업을 수행 할 때는 AUTO 또는 PATH 옵션을 선택해야합니다 . 이 기사에는 AUTO 옵션 사용 예가 포함되어 있습니다.

 

문법

구문은 다음과 같습니다.

SELECT ...
  (여기에 쿼리구문 작성...)
FOR JSON AUTO;

기본적으로 FOR JSON AUTO 를 쿼리 끝에 추가 하기 만 하면됩니다.

Example 1 – 기본 사용

실행쿼리 구문

USE Music;
SELECT TOP 3 AlbumName, ReleaseDate
FROM Albums
FOR JSON AUTO;

결과내역

[
    {
        "AlbumName": "Powerslave",
        "ReleaseDate": "1984-09-03"
    },
    {
        "AlbumName": "Powerage",
        "ReleaseDate": "1978-05-05"
    },
    {
        "AlbumName": "Singing Down the Lane",
        "ReleaseDate": "1956-01-01"
    }
]

따라서 결과는 행과 열이 아닌  JSON 형태로 나타납니다.

한줄짜리 결과내용?

결과내용은 처음에 단일 행과 단일 열에 다음과 같이 하나의 긴 행으로 나타날 수 있습니다.

 

Example 2 – 여러 테이블 조인 구문쿼리

이 예에서는 일대 다 관계 가 있는 두 테이블을 쿼리 합니다. 이 경우 각 아티스트는 많은 앨범을 가질 수 있습니다.

USE Music;
SELECT 
  ArtistName, 
  AlbumName
FROM Artists
  INNER JOIN Albums
    ON Artists.ArtistId = Albums.ArtistId
ORDER BY ArtistName
FOR JSON AUTO;

결과

[
    {
        "ArtistName": "AC/DC",
        "Albums": [
            {
                "AlbumName": "Powerage"
            }
        ]
    },
    {
        "ArtistName": "Allan Holdsworth",
        "Albums": [
            {
                "AlbumName": "All Night Wrong"
            },
            {
                "AlbumName": "The Sixteen Men of Tain"
            }
        ]
    },
    {
        "ArtistName": "Buddy Rich",
        "Albums": [
            {
                "AlbumName": "Big Swing Face"
            }
        ]
    },
    {
        "ArtistName": "Devin Townsend",
        "Albums": [
            {
                "AlbumName": "Ziltoid the Omniscient"
            },
            {
                "AlbumName": "Casualties of Cool"
            },
            {
                "AlbumName": "Epicloud"
            }
        ]
    },
    {
        "ArtistName": "Iron Maiden",
        "Albums": [
            {
                "AlbumName": "Powerslave"
            },
            {
                "AlbumName": "Somewhere in Time"
            },
            {
                "AlbumName": "Piece of Mind"
            },
            {
                "AlbumName": "Killers"
            },
            {
                "AlbumName": "No Prayer for the Dying"
            }
        ]
    },
    {
        "ArtistName": "Jim Reeves",
        "Albums": [
            {
                "AlbumName": "Singing Down the Lane"
            }
        ]
    },
    {
        "ArtistName": "Michael Learns to Rock",
        "Albums": [
            {
                "AlbumName": "Blue Night"
            },
            {
                "AlbumName": "Eternity"
            },
            {
                "AlbumName": "Scandinavia"
            }
        ]
    },
    {
        "ArtistName": "The Script",
        "Albums": [
            {
                "AlbumName": "No Sound Without Silence"
            }
        ]
    },
    {
        "ArtistName": "Tom Jones",
        "Albums": [
            {
                "AlbumName": "Long Lost Suitcase"
            },
            {
                "AlbumName": "Praise and Blame"
            },
            {
                "AlbumName": "Along Came Jones"
            }
        ]
    }
]

보시다시피, 각 album 은 “Albums” 아래에 중첩되어 있습니다. AUTO 옵션이 SELECT 목록의 컬럼 순서와 테이블을 기반으로 출력을 결정 하기 때문 입니다

Example 3 – 하나의 Root Node 추가하기

이 ROOT() 옵션을 사용하여 루트 노드를 출력에 추가 할 수 있습니다 . 이렇게하면 단일 최상위 요소가 출력에 추가됩니다. 이렇게 하려면 ROOT() 루트 노드에 원하는 이름으로 쿼리 끝에 옵션을 추가하면 됩니다.

따라서 이전 예제를 다음과 같이 수정할 수 있습니다.

USE Music;
SELECT TOP 3
  ArtistName, 
  AlbumName
FROM Artists
  INNER JOIN Albums
    ON Artists.ArtistId = Albums.ArtistId
ORDER BY ArtistName
FOR JSON AUTO, ROOT('Music');

결과

{
    "Music": [
        {
            "ArtistName": "AC/DC",
            "Albums": [
                {
                    "AlbumName": "Powerage"
                }
            ]
        },
        {
            "ArtistName": "Allan Holdsworth",
            "Albums": [
                {
                    "AlbumName": "All Night Wrong"
                },
                {
                    "AlbumName": "The Sixteen Men of Tain"
                }
            ]
        }
    ]
}

 

Example 4 – Array Wrapper 제거하기

WITHOUT_ARRAY_WRAPPER 옵션을 사용 하여 결과를 둘러싸는 대괄호 [] 를 제거 할 수 있습니다 .

예제

USE Music;
SELECT TOP 1 
  AlbumName, 
  ReleaseDate
FROM Albums
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

결과

 

{
    "AlbumName": "Powerslave",
    "ReleaseDate": "1984-09-03"
}

 

원문 참고자료

https://database.guide/sql-server-for-json-auto-examples-t-sql/

SQL Server FOR JSON AUTO Examples (T-SQL) | Database.Guide

https://docs.microsoft.com/ko-kr/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15

FOR JSON을 사용하여 쿼리 결과를 JSON으로 서식 지정 - SQL Server