#title Json
[[TableOfContents]]

웬만하면 쓰지말자. JSON

==== Newtonsoft.Json.Net20.dll 를 DB에 등록한다. ====
{{{
create assembly Udf_JsonParser
from 'c:\Newtonsoft.Json.Net20.dll'
WITH PERMISSION_SET = UNSAFE
go
}}}

==== 대략 C#으로 짠다. ====
{{{
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\
}}}

==== DB에 어셈블리 등록하고, 함수 만든다. ====
{{{
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
}}}