SQL Server 2016 의 JSON 지원 가능 함수 알아보기


https://www.sqlshack.com/native-json-support-in-sql-server-2016/


SQL Server 2016이 도입됨에 따라 Microsoft는 SQL Server를 한 단계 더 발전시킨 많은 새로운 기능을 도입했으며 많은 주요 관계형 데이터베이스 시스템에 선보일 수 있도록했습니다.

부족했지만 필사적으로 필요한 기능 중 하나는 JSON에 대한 기본 지원이었습니다.

SQL Server 2016 이전에는 이미 JSON을 지원하는 많은 데이터베이스가있었습니다.

PostgreSQL, Oracle 및 MongoDB는 기본적으로 JSON을 기본적으로 지원하는 다른 많은 데이터베이스에서 언급되지 않습니다.

JSON이란 무엇입니까?

JSON은 JavaScript Object Notation의 머리 글자 어입니다. 그것은 많은 기능 때문에 웹 개발자들 사이에서 매우 인기가있었습니다. 텍스트 기반이기 때문에 가볍고 읽기 쉽기 때문에 JSON을 웹 개발에서 매우 유명하게 만들었습니다.

SQL 2016에서 JSON을 지원하기 위해 도입 된 기능은 무엇입니까?

SQL Server 2016에서 기본적으로 JSON을 지원하기 위해 몇 가지 기능이 SQL 2016과 함께 도입되었습니다. 이러한 기능은 다음과 같습니다.

  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • OPENJSON
  • FOR JSON

** 참고 : XML과 달리 SQL Server에는 JSON을 수용 할 특정 데이터 유형이 없습니다. 따라서 NVARCHAR을 사용해야합니다.

우리는 기능에 대해 자세히 살펴볼 것입니다.

먼저 변수를 선언하고 JSON 문자열을 할당하는 방법을 살펴 보겠습니다.

문자열 값을 NVARCHAR 유형 변수에 할당하는 것만 큼 간단합니다.

이제 이전에 언급 한 각 기능에 대해 자세히 살펴 보겠습니다. 우리는 그것이하는 일과 T-SQL 문에서 어떻게 사용 하는지를 볼 것입니다.

ISJSON 기능

이것은 SQL Server에서 JSON을 지원하는 가장 간단한 함수입니다. 하나의 문자열 인수를 입력으로 사용하여 유효성을 검사하고 BIT를 반환합니다. 제공된 JSON이 유효한 경우 1을, 그렇지 않은 경우 0을 반환합니다.

제공된 입력 인수가 NULL이면 리턴 값도 NULL입니다.

통사론:

예:

이 명령문을 실행하면 제공된 JSON 문자열이 유효하기 때문에 1을 반환하는 것을 볼 수 있습니다.

그러나 ISJSON을 사용하여 유효성을 검사 할 때 우려가 있습니다. ISJSON은 키가 고유한지 여부를 검증하지 않습니다.

예:

동일한 JSON 문자열을 사용하지만 이번에는 'FirstName'키를 복제합니다.

위의 코드를 실행하면 JSON 문자열에도 중복 키가 포함되어 있어도 반환 값은 1로 유지됩니다. 대부분의 JSON 검사기는 이러한 종류의 JSON 문자열을 유효하지 않은 문자열로 찾습니다. (예 : http://jsonlint.com/ )

추가 읽기는 ISJSON (Transact-SQL) 에서 수행 할 수 있습니다.

JSON_VALUE 함수

JSON_VALUE는 요청 된 키에 대한 JSON 문자열에서 스칼라 값을 반환합니다. 이를 설명하기 위해 배열을 포함하는 다른 JSON 문자열을 사용합니다.

위의 세부 정보를 사용하면 직원의 이름과 직원의 아들 이름을 가져옵니다. 아래에 표시된 구문을 사용하여이를 수행 할 수 있습니다.

그러면 'John'과 'James'가 각각 반환됩니다. 여기에 언급 할 사항이 거의 없습니다.

  • 배열 색인은 0으로 시작합니다. 그러므로 우리는 두 번째 요소 인 아들의 이름을 얻기 위해 첫 번째 색인을 원한다고 말할 필요가 있습니다. 인덱스가 범위를 벗어난 경우 NULL을 반환합니다.
  • JSON 경로는 대소 문자를 구분합니다. 따라서 JSON 문자열에있는 내용과 정확하게 일치해야합니다. 경로가 발견되지 않으면 NULL을 리턴합니다.

예를 들어 이들 모두는 NULL을 리턴합니다.

그러나 때때로 NULL을 반환하는 이유를 알아야합니다. JSON 경로의 기본 값이 NULL 값을 갖는 경우 일 수 있습니다. 따라서 이들을 구별하기 위해 JSON 경로 앞에 'strict'키워드를 포함시켜야합니다.

이 작업을 실행하면 다음과 같은 오류 메시지가 나타납니다.

JSON_QUERY 함수

JSON_QUERY 함수는 지정된 JSON 문자열에서 배열 문자열로 세부 정보를 추출하고 반환합니다. 이것을 설명하기 위해 앞의 예제에서 사용한 것과 같은 JSON 문자열을 사용합니다.

첫 번째 쿼리는 EmployeeInfo 경로 아래에있는 세부 정보를 반환합니다.

두 번째 쿼리는 Dependents 경로 아래에있는 세부 정보를 반환합니다.

세 번째 쿼리는 종속 경로 아래의 첫 번째 요소와 관련된 세부 정보를 반환합니다. 따라서 위의 것과는 달리 첫 번째 세부 정보 만 반환합니다.

JSON_MODIFY 함수

이것은 SQL Server에서 사용할 수있는 xml.modify () 기능과 매우 유사합니다. JSON_MODIFY 함수는 JSON 문자열의 속성에 기존 값을 추가하는 데 사용됩니다. 이름이 기존 값을 수정하는 아이디어를 반영하더라도 세 가지 방법으로 사용할 수 있습니다.

  • 값을 업데이트하려면
  • 값을 삭제하려면
  • 값을 삽입하려면

JSON_MODIFY 함수를 사용하여 JSON 문자열의 속성 값 업데이트

먼저 JSON_MODIFY 함수를 사용하여 기존 JSON 속성 값을 업데이트하는 방법을 살펴 보겠습니다. 업데이트 할 때 두 가지를 제공해야합니다.

  • 재산의 정확한 경로
  • 업데이트해야하는 값입니다.

예:

위에서 사용 된 것과 동일한 JSON 세부 정보를 고려하고 이름과 성을 각각 'James Williams'로 업데이트합니다.

앞서 언급 한 쿼리가 실행되면 결과 창에 다음 내용이 표시됩니다.

JSON_MODIFY 함수를 사용하여 JSON 문자열의 속성 값 삭제

이제 기존 JSON 문자열에서 속성을 제거 할 수있는 방법을 살펴 보겠습니다. 위의 업데이트 예제와 비교할 때 볼 수있는 유일한 차이점은 두 번째 매개 변수가 NULL로 전달되어야한다는 것입니다. 우리는 위와 동일한 예제를 사용하고 'AnnualSalary'속성을 제거합니다.

@vJson 변수에 저장된 값을 인쇄하면 'AnnualSalary'속성이 완전히 제거되었음을 알 수 있습니다.

JSON_MODIFY 함수를 사용하여 JSON 문자열에 속성 삽입

이제 기존 JSON 문자열에 속성을 삽입하는 방법에 대한 예제를 살펴 보겠습니다. 속성을 삽입 할 때 구문은 JSON 문자열을 업데이트하는 것과 유사하지만 제공된 경로가 JSON에 존재해서는 안됩니다. 그렇지 않으면 JSON_MODIFY가 기존의 기존 속성을 수정하는 데 사용되어 함수가 작동합니다. 따라서 새 속성을 삽입하는 대신 제공된 경로에서 값을 대체합니다.

삽입 할 때 JSON 문자열에 값을 삽입 할 수있는 두 가지 방법이 있습니다.

  • 속성 / 값으로 삽입 가능
  • 새로운 배열 요소로 삽입 가능

우리는이 두 가지 방법을 어떻게 사용할 수 있는지 보게 될 것입니다.

JSON에 새로운 Property / Value 삽입하기.

앞의 예제에서 사용한 것과 동일한 JSON 예제를 사용하고 'Doj'라는 속성을 삽입합니다. ( 'Doj'는 가입 날짜를 나타냄)

앞서 설명한 코드 스 니펫을 실행하면 새 속성이 추가 된 것을 볼 수 있습니다.

JSON에 새 값을 새 요소로 삽입.

기존 JSON에 새 값을 삽입하는 다른 방법은이를 새 요소로 추가하는 것입니다. 이것은 다음 구문을 사용하여 수행 할 수 있습니다. 우리는 같은 예를 사용하고 새로운 의존성을 추가 할 것입니다.

그러나 위의 구문을 실행하면 새로운 요소가 JSON 문자열에 추가되지만 큰 따옴표 문자와 함께 많은 이스케이프 문자가 추가됩니다.

이 자동 이스케이프 문자에 관한 MSDN에서 설명한대로 정상입니다. 자동 이스케이프 문자없이 새 요소를 추가하려면 아래와 같이 JSON_QUERY 함수로 값을 래핑하십시오.

자세한 내용은 여기에서 확인할 수 있습니다. - JSON_MODIFY (Transact-SQL)

OPENJSON 기능

앞에서 설명한 함수와 달리 OPENJSON은 테이블 값 함수입니다. 즉 단일 값이 아닌 테이블 또는 행 모음을 반환한다는 의미입니다. 이렇게하면 JSON 객체 배열을 반복하고 각 요소에 대한 행을 채 웁니다.

이 함수는 세부 정보를 다음 정보가 들어있는 결과 집합으로 반환합니다.

  • 키 → 속성의 키 이름
  • 값 → 위의 키의 기본 값
  • 유형 → 값의 데이터 유형

이것은 두 가지 방법으로 사용될 수 있습니다.

  • 미리 정의 된 스키마가 없으면
  • 잘 정의 된 스키마 사용

사전 정의 된 스키마 없이 OPENJSON 사용

이것을 설명하기 위해 앞의 예제에서 사용한 JSON 문자열과 다른 JSON 문자열을 사용합니다.

이것을 실행하면 (자), 다음의 결과 세트가 돌려 주어집니다.

정의 된 스키마가 있는 OPENJSON의 사용법

이 방법을 사용할 때 각 데이터 유형과 함께 열 이름 목록을 제공해야합니다. 이전 방법에 비해 잘 정의 된 스키마를 사용하면 얻을 수있는 장점 중 하나는 각 속성 값에 대해 서로 다른 열을 포함하는 결과 집합을 생성 할 수 있다는 것입니다.

좀 더 현실적인 JSON 문자열을 사용하여이를 달성하는 방법을 살펴보고 먼저 열 이름을 변경하지 않고 세부 정보를 가져올 수있는 방법을 살펴 보겠습니다. 우리는 아래와 같이 스키마 세부 사항을 전달해야합니다.

위의 코드를 실행하면 다음 결과 집합이됩니다.

이제 OPENJSON 함수를 사용하여 세부 정보를 가져올 때 열 이름을 어떻게 변경할 수 있는지 살펴 보겠습니다. 이전에 표시된 예제와 유사합니다. 차이점은 열 이름이 표시되어야하고 각 데이터 유형 다음에 정확한 JSON 경로가 와야한다는 것입니다. ** 경로는 대소 문자를 구분합니다. ) 올바른 경로를 제공하지 않으면 결과 집합에서 해당 열 아래에 NULL이 반환됩니다.

위의 코드를 실행하면 다음 결과 집합이됩니다.

Order No의 경로가 잘못 제공되는 아래 예를 참조하십시오.

FOR JSON 함수

FOR JSON 기능은 SQL Server에서 사용할 수있는 FOR XML 기능과 거의 비슷합니다. 표 형식의 데이터를 JSON 형식으로 내보내는 데 사용됩니다.

각 행은 JSON 객체로 변환되고 셀의 데이터는 해당 JSON 객체의 값으로 변환됩니다. 열 이름 / 별칭이 키 이름으로 사용됩니다.

JSON 기능을 사용할 수있는 방법에는 두 가지가 있습니다.

  • JSON AUTO 용
  • JSON 경로 용

FOR JSON AUTO

이 기능은 FOR XML AUTO와 유사합니다. FOR JSON AUTO는 쿼리에 사용 된 테이블 계층을 기반으로 중첩 된 JSON 하위 배열을 자동으로 생성하여 테이블 형식의 데이터를 내 보냅니다.

앞서 설명한 것을 설명하기 위해 우리는 몇 가지 Order Details로 구성된 간단한 데이터 구조를 사용할 것입니다.

다음 쿼리를 실행하십시오.

앞서 언급 한 쿼리를 실행하면 다음과 같은 결과가 생성됩니다.

열 별칭을 사용하지 않았기 때문에 정확한 열 이름이 키로 사용되었습니다. 열 별칭을 사용하면 별칭이 특성 키로 사용됩니다. 아래의 예를 참조하십시오 :

앞서 언급 한 쿼리를 실행하면 다음과 같은 결과가 나타납니다.

열 별칭이 속성 키로 사용 된 방법을 관찰하십시오.

FOR JSON AUTO에 대해 설명 된 두 예제 모두에서 생성 된 JSON에 루트 요소가 포함되어 있지 않음을 알 수 있습니다. 이는 JSON에 루트 요소로 결과를 내보내라는 지시를하지 않았기 때문입니다. 다음과 같이 루트 요소를 사용하여 세부 정보를 내보낼 수 있습니다.

FOR JSON PATH

앞의 예에서 AUTO 옵션과 결합 된 경우 주문 라인 세부 정보가 분리 된 JSON 객체로 내보내 졌는지 확인할 수 있습니다.

그러나 PATH 옵션을 사용하면 SELECT 문을 가져 오는 방식으로 세부 정보가 추출됩니다. 이는 주문 라인 정보를 별도의 객체로 가져 오는 대신 Order Header 정보와 함께 두 개의 다른 요소로 반환됩니다.

그러나 FOR JSON PATH 옵션을 사용하여 세부 정보를 내보낼 때주의해야 할 중요한 사항이 하나 있습니다. 즉, 여러 테이블에 동일한 열 이름을 사용하면 이러한 열을 하나만 가져올 수 있습니다. 이 두 열을 모두 포함하면 오류가 발생합니다.

예를 들어 Header와 Detail 테이블 모두에서 OrderId를 내보내려고하면 오류가 발생합니다.

더보기

데이터베이스 개발자 생산성을 향상시키는 SQL Server 용 무료 도구를 고려하십시오 .

관련된 링크들


Comments 0