_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › Json

Contents

1 Newtonsoft.Json.Net20.dll 襯 DB 焔.
2 C#朱 讌.
3 貉危 .
4 DB 伎觚襴 焔螻, 襷.
5 企.


襷覃 一襷. JSON

1 Newtonsoft.Json.Net20.dll 襯 DB 焔. #

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

2 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);
    }
};

3 貉危 . #

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\

4 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

5 企. #

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
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

レ ろ 蟆 豢ろ螻 覦襯 蠍碁 螳. 谿語朱 螳 豈 蟆 訖企. 襯 覲 蟇 る讌 訖企. (磯襴一)