[MSSQL] JSON 파싱하기

DB/MSSQL

[MSSQL] JSON 파싱하기

MAKGA 2021. 6. 11. 21:12

1. ISJSON : 문자열에 유효한 JSON이 포함되어 있는지 여부를 확인

syntax: ISJSON ( expression )

example 1	example 2
DECLARE @param </data type> SET @param = data IF (ISJSON(@param) > 0) BEGIN -- Do something with the valid JSON value of @param. END	SELECT id, json_col FROM tab1 WHERE ISJSON(json_col) > 0

2. JSON_VALUE : JSON 문자열에서 스칼라 값을 추출

syntax: JSON_VALUE ( expression , path )

query

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{
"info":{
  "type":1,
  "address":{
  "town":"Bristol",
"county":"Avon",
"country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}'

DECLARE @town NVARCHAR(MAX)

SET @town = JSON_VALUE(@jsonInfo, '$.info.type');
SELECT @town AS InfoType
SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town');
SELECT @town AS Town
SET @town = JSON_VALUE(@jsonInfo, '$.info.tags[1]');
SELECT @town AS Tags2
SET @town = JSON_VALUE(@jsonInfo, '$.type');
SELECT @town AS Type

3. JSON_QUERY : JSON 문자열에서 개체 또는 배열을 추출합니다.

syntax: JSON_QUERY ( expression [ , path ] )

query

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{
"info":{
  "type":1,
  "address":{
  "town":"Bristol",
  "county":"Avon",
  "country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}'

SELECT JSON_QUERY(@jsonInfo,'$') AS FullText
SELECT JSON_QUERY(@jsonInfo,'$.info') AS Info
SELECT JSON_QUERY(@jsonInfo,'$.info.address') AS Address
SELECT JSON_QUERY(@jsonInfo,'$.info.tags') AS Tags

4. JSON_MODIFY : JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.

syntax: JSON_MODIFY ( expression , path , newValue )

query

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{
"info":{
  "type":1,
  "address":{
  "town":"Bristol",
  "county":"Avon",
  "country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}'
PRINT @jsonInfo

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.info.type',2)
PRINT @jsonInfo

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.info.address.town','Seoul')
PRINT @jsonInfo

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.type','Special')
PRINT @jsonInfo

result

-- Original
{
  "info":{
    "type":1,
      "address":{
      "town":"Bristol",
      "county":"Avon",
      "country":"England"
      },
      "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}
-- info.type : 1 -> 2
{
  "info":{
      "type":2,
      "address":{
  "town":"Bristol",
  "county":"Avon",
  "country":"England"
  },
      "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}
-- town : Bristol -> Seoul
{
  "info":{
  "type":2,
"address":{
  "town":"Seoul",
  "county":"Avon",
  "country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}
-- type : Basic -> Special
{
  "info":{
      "type":2,
      "address":{
      "town":"Seoul",
      "county":"Avon",
      "country":"England"
      },
      "tags":["Sport", "Water polo"]
  },
  "type":"Special"
}

다중 수정을 한번에 하려면 다음과 같이 함수를 여러번 호출해야함

query

SET @jsonInfo = JSON_MODIFY(
JSON_MODIFY(
JSON_MODIFY(@jsonInfo, '$.type','Special'),
'$.info.address.town','Seoul'),
'$.info.type',2)

result

{
"info":{
  "type":1,
  "address":{
  "town":"Bristol",
  "county":"Avon",
  "country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Basic"
}
{
  "info":{
  "type":2,
"address":{
      "town":"Seoul",
  "county":"Avon",
  "country":"England"
  },
  "tags":["Sport", "Water polo"]
  },
  "type":"Special"
}

키 이름 바꾸기 - 기존 속성을 복사해 추가하고, 기존 값을 NULL로 변경해서 삭제함

query

SET @jsonInfo = JSON_MODIFY(
JSON_MODIFY(@jsonInfo,'$.Type',
CAST(
JSON_VALUE(@jsonInfo,'$.type')
AS NVARCHAR(MAX))
)
,'$.type',NULL)

desc

1. JSON_VALUE(@jsonInfo,'$.type')로 type의 값을 가져와서
2. CAST(@ AS NVARCHAR(MAX))의 형태로 변환한다음
3. JSON_MODIFY(@jsonInfo,'$.Type',)으로 새로운 키:값을 설정해준 뒤
4. JSON_MODIFY('$.type',NULL)으로 기존 값을 NULL로 변경해주면 끝

result

{
    "info":{
        "type":1,
        "address":{
            "town":"Bristol",
            "county":"Avon",
            "country":"England"
        },
        "tags":["Sport", "Water polo"]
    },
    "type":"Basic"
}
{
    "info":{
        "type":1,
        "address":{
            "town":"Bristol",
            "county":"Avon",
            "country":"England"
        },
        "tags":["Sport", "Water polo"]
    },
"Type":"Basic"
}

값 증분 - 기존 값을 복사해 추가하고, 기존 값을 NULL로 변경해서 삭제함

query

SET @jsonInfo=JSON_MODIFY(
@jsonInfo,'$.info.type',
CAST(
JSON_VALUE(@jsonInfo,'$.info.type')
AS INT)+1)

desc

1. 기존 값을 가져와서
2. INT형으로 변환한 뒤 + 1을 한 값을
3. JSON_MODIFY(@jsonInfo,'$.info.type',NewValue)로 저장

result

{
    "info":{
        "type":1,
        "address":{
            "town":"Bristol",
            "county":"Avon",
            "country":"England"
        },
        "tags":["Sport", "Water polo"]
    },
    "type":"Basic"
}
{
    "info":{
        "type":2,
        "address":{
            "town":"Bristol",
            "county":"Avon",
            "country":"England"
        },
        "tags":["Sport", "Water polo"]
    },
    "type":"Basic"
}

데이터 집합(?) 수정

query

SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.tags',JSON_QUERY('["game", "watch"]'))

desc

SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.tags','["game", "watch"]')으로 할 경우 자동이스케이프가 붙으니 JSON_QUERY 사용필요

result

출처:

https://docs.microsoft.com/ko-kr/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

JSON 데이터 작업 - SQL Server

SQL Server의 JSON 데이터

docs.microsoft.com

'DB > MSSQL' 카테고리의 다른 글

[MSSQL] 호환성 수준 (0)	2021.06.17
[MSSQL] 이중화 방식 (0)	2021.06.15
[MSSQL] 구분자로 구분된 문자열 분리하기 (0)	2021.06.11
[MSSQL] OPENQUERY 작성중 (0)	2021.06.09
[MSSQL] REST API 호출하기 (0)	2021.06.09

현재글[MSSQL] JSON 파싱하기

신입부터의 기록