MSSQL] 구버전 SSMS 에서 JSON 파싱

최신 SSMS에서는 OPENJSON 함수를 사용하여, JSON String의 key와 값 추출이 가능하다. 

OPENJSON을 제공하지 않는 구버전 SSMS는 아래 함수 만들어서 사용가능하다. 

 

create FUNCTION [dbo].[fnGetJsonValue](@key varchar(100), @data nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
  DECLARE @keyJson varchar(105) = '"' + @key+ '":'
  DECLARE @keyIdx int = CHARINDEX(@keyJson, @data)
  IF @keyIdx = 0 RETURN null

  DECLARE @valueIdx int = @keyIdx + LEN(@keyJson)
  DECLARE @termIdx int = CHARINDEX('"', @data, @valueIdx)

  IF @termIdx <> 0 BEGIN
    SET @valueIdx = @valueIdx + 1
    SET @termIdx = CHARINDEX('"', @data, @valueIdx)

    -- Overcome JSON qoute escape
    WHILE SUBSTRING(@data, @termIdx-1, 1) = '\'
    BEGIN
      SET @termIdx = CHARINDEX('"', @data, @termIdx + 1)
    END
  END ELSE BEGIN
    SET @termIdx = CHARINDEX(',', @data, @valueIdx)
    IF @termIdx = 0 SET @termIdx = CHARINDEX('}', @data, @valueIdx)
  END

  IF @termIdx = 0 RETURN null

  -- Replace escapte quote before return value
  RETURN REPLACE(SUBSTRING(@data, @valueIdx, @termIdx - @valueIdx), '\"', '"')
END