Contents

1 Newtonsoft.Json.Net20.dll 를 DB에 등록한다.

2 대략 C#으로 짠다.

3 컴파일 한다.

4 DB에 어셈블리 등록하고, 함수 만든다.

5 잘 쓴다.

create assembly Udf_JsonParser
from 'c:\Newtonsoft.Json.Net20.dll'
WITH PERMISSION_SET = UNSAFE
go

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
//using Microsoft.CSharp;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Json(string _json, string _key)
    {
        JObject obj = (JObject)JsonConvert.DeserializeObject(_json);
        return new SqlString(obj[_key].ToString());
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JsonArray(string _json, string _key, int _s)
    {
        JObject obj = (JObject)JsonConvert.DeserializeObject(_json);
        JArray arr = (JArray)obj[_key];
        return new SqlString(arr[_s].ToString());
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 JsonArraySize(string _json, string _key)
    {
        JObject obj = (JObject)JsonConvert.DeserializeObject(_json);
        JArray arr = (JArray)obj[_key];
        return new SqlInt32(arr.Count);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JsonType(string _json, string _key)
    {
        JObject obj = (JObject)JsonConvert.DeserializeObject(_json);
        return new SqlString(obj[_key].Type.ToString());
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JsonObjectToString(string _json, string _key)
    {
        JObject obj = (JObject)JsonConvert.DeserializeObject(_json);
        return new SqlString(JsonConvert.SerializeObject(obj[_key]).ToString());
    }
};

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
//using Microsoft.CSharp;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetValue(string _json, string _key)
    {
        string val = "";
        _json = _json.Replace(" : ", ":").Replace(" :", ":").Replace(": ", ":");
        int key_position = _json.IndexOf(_key);
        int sep_position = _json.IndexOf(":", key_position + _key.Length) + 1;
        int val_position = _json.IndexOf(",", sep_position + 1);
        //마지막이면..
        if (val_position == -1) 
            val_position = _json.IndexOf("}", sep_position + 1);

        if (_json.Substring(sep_position, 1) == "{")
        {
            val_position = _json.IndexOf("}", sep_position) + 1;
            val = _json.Substring(sep_position, val_position - sep_position);
        }
        else if (_json.Substring(sep_position, 1) == "[")
        {
            val_position = _json.IndexOf("]", sep_position) + 1;
            val = _json.Substring(sep_position, val_position - sep_position);
        }
        else
            val = _json.Substring(sep_position, val_position - sep_position);


        val = val.Replace("\"", "");
        return new SqlString(val);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ArrayGetValue(string _json, string _key, int _s)
    {
        string[] val_arrary;
        string val = "";
        _json = _json.Replace(" : ", ":").Replace(" :", ":").Replace(": ", ":");
        int key_position = _json.IndexOf(_key);
        int sep_position = _json.IndexOf(":", key_position + _key.Length) + 1;
        int val_position = _json.IndexOf(",", sep_position + 1);
        //마지막이면..
        if (val_position == -1) 
            val_position = _json.IndexOf("}", sep_position + 1);

        val_position = _json.IndexOf("]", sep_position) + 1;
        val = _json.Substring(sep_position, val_position - sep_position);
        val_arrary = val.Replace("[", "").Replace("]", "").Replace("\"", "").Replace(" ", "").Split(',');

        return new SqlString(val_arrary[_s]);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 ArraySize(string _json, string _key)
    {
        string[] val_arrary = { };
        string val = "";
        _json = _json.Replace(" : ", ":").Replace(" :", ":").Replace(": ", ":");
        int key_position = _json.IndexOf(_key);
        int sep_position = _json.IndexOf(":", key_position + _key.Length) + 1;
        int val_position = _json.IndexOf(",", sep_position + 1);
        //마지막이면..
        if (val_position == -1) val_position = _json.IndexOf("}", sep_position + 1);

        val_position = _json.IndexOf("]", sep_position + 1);
        val = _json.Substring(sep_position, val_position - sep_position);
        val_arrary = val.Replace("[", "").Replace("]", "").Replace("\"", "").Replace(" ", "").Split(',');

        return new SqlInt32(val_arrary.Length);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JsonType(string _json, string _key)
    {
        string valueType = "String";

        _json = _json.Replace(" : ", ":").Replace(" :", ":").Replace(": ", ":");
        int key_position = _json.IndexOf(_key);
        int sep_position = _json.IndexOf(":", key_position + _key.Length) + 1;
        int val_position = _json.IndexOf(",", sep_position + 1);
        //마지막이면..
        if (val_position == -1) val_position = _json.IndexOf("}", sep_position + 1);

        //값의 타입
        string first_string = _json.Substring(sep_position, 1);
        if (first_string == "[") valueType = "Array";
        else if (first_string == "{") valueType = "Object";

        return new SqlString(valueType);
    }
};

cd C:\Windows\Microsoft.NET\Framework64\v3.5
csc.exe /t:library /r:Newtonsoft.Json.Net20.dll "c:\clr\Udf_JsonParser.cs"
copy Udf_JsonParser.dll c:\clr\

drop function Udf_JsonParserObjectToString
drop function Udf_JsonParserType
drop function Udf_JsonParserArraySize
drop function Udf_JsonParserArray
drop function Udf_JsonParser
drop assembly Udf_JsonParser
go

create assembly Udf_JsonParser
from 'c:\clr\Udf_JsonParser.dll'
go

create function Udf_JsonParser (@json nvarchar(4000), @key nvarchar(100)) returns nvarchar(100)
external name Udf_JsonParser.UserDefinedFunctions.Json; --대소문자 구문함
go

create function Udf_JsonParserArray (@json nvarchar(4000), @key nvarchar(100), @i int) returns nvarchar(100)
external name Udf_JsonParser.UserDefinedFunctions.JsonArray; --대소문자 구문함
go

create function Udf_JsonParserArraySize (@json nvarchar(4000), @key nvarchar(100)) returns int
external name Udf_JsonParser.UserDefinedFunctions.JsonArraySize; --대소문자 구문함
go

create function Udf_JsonParserType (@json nvarchar(4000), @key nvarchar(100)) returns nvarchar(100)
external name Udf_JsonParser.UserDefinedFunctions.JsonType; --대소문자 구문함
go

create function Udf_JsonParserObjectToString (@json nvarchar(4000), @key nvarchar(100)) returns nvarchar(4000)
external name Udf_JsonParser.UserDefinedFunctions.JsonObjectToString; --대소문자 구문함
go

declare @json nvarchar(4000)
set @json = '{
			   "이름": "테스트",
			   "나이": 25,
			   "성별": "여",
			   "기혼": true,
			   "주소": "서울특별시 양천구 목동",
			   "특기": ["농구", "도술"],
			   "가족관계": {"#": 2, "아버지": "홍판서", "어머니": "춘섬"},
			   "회사": "경기 안양시 만안구 안양7동"
			}'

--일반적인 값
select 
    ods.dbo.Udf_JsonParser(@json, '이름') 이름
,   ods.dbo.Udf_JsonParser(@json, '나이') 나이
,   ods.dbo.Udf_JsonParser(@json, '기혼') 기혼여부
,   ods.dbo.Udf_JsonParser(@json, '주소') 주소
,   ods.dbo.Udf_JsonParser(@json, '회사') 주소

--Json 타입
select ods.dbo.Udf_JsonParserType(@json, '특기')

--배열크기
select ods.dbo.Udf_JsonParserArraySize(@json, '특기')

--배열값
select ods.dbo.Udf_JsonParserArray(@json, '특기', 0)

--Json 타입
select ods.dbo.Udf_JsonParserType(@json, '가족관계')

--Json 객체
select ods.dbo.Udf_JsonParserObjectToString(@json, '가족관계')


select 
    ods.dbo.Udf_JsonParser(json, '#')
,   ods.dbo.Udf_JsonParser(json, '아버지')
,   ods.dbo.Udf_JsonParser(json, '어머니')
from (select ods.dbo.Udf_JsonParserObjectToString(@json, '가족관계') json) t