T-SQL Tips: 解析Json字符串
来源:互联网 发布:成人用品淘宝店要求 编辑:程序博客网 时间:2024/05/17 03:17
定义一个用户自定义函数,用来解析Json字符串。
代码如下:
SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER ON;GOIF NOT EXISTS(select * from sys.objects where object_id = OBJECT_ID(N'dbo.ParseJSON') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT', N'F'))BEGINEXEC('CREATE FUNCTION dbo.ParseJSON() RETURNS @hierarchy table( id int ) AS BEGIN RETURN END;');PRINT 'FUNCTION dbo.ParseJSON is created.';ENDGO/* ==========================================================================Description: Parse JSON string to a table style hierarchy.Input params: nvarchar(max)Output param: table========================================================================== */ALTER FUNCTION dbo.ParseJSON( @json nvarchar(max) )RETURNS @hierarchy table( element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */ object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ name nvarchar(2000), /* the name of the object */ stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */ valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/ bigintvalue bigint) AS BEGINDECLARE @firstobject int, --the index of the first open bracket found in the JSON string@opendelimiter int, --the index of the next open bracket found in the JSON string@nextopendelimiter int,--the index of subsequent open bracket found in the JSON string@nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string@type nvarchar(10),--whether it denotes an object or an array@nextclosedelimiterChar CHAR(1),--either a '}' or a ']'@contents nvarchar(MAX), --the unparsed contents of the bracketed expression@start int, --index of the start of the token that you are parsing@end int,--index of the end of the token that you are parsing@param int,--the parameter at the end of the next Object/Array token@endofname int,--the index of the start of the parameter at end of Object/Array token@token nvarchar(4000),--either a string or object@value nvarchar(MAX), -- the value as a string@name nvarchar(200), --the name as a string@parent_id int,--the next parent ID to allocate@lenjson int,--the current length of the JSON String@characters NCHAR(62),--used to convert hex to decimal@result BIGINT,--the value of the hex symbol being parsed@index SMALLINT,--used for parsing the hex value@escape int; --the index of the next escape character/* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' * in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in * the JSON string by tokens representing the string */DECLARE @strings table(string_id int IDENTITY(1, 1),stringvalue nvarchar(MAX))/* initialise the characters to convert hex to ascii */SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';SET @parent_id = 0;/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */WHILE 1 = 1 /* forever until there is nothing more to do */BEGINSET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */IF @start = 0 BREAK; /*no more so drop through the WHILE loop */IF SUBSTRING(@json, @start+1, 1) = '"'BEGIN /* Delimited name */SET @start = @start+1;SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);ENDIF @end = 0 /*no end delimiter to last string*/BREAK; /* no more */SELECT @token = SUBSTRING(@json, @start+1, @end-1)/* now put in the escaped control characters */SELECT @token = REPLACE(@token, from_string, to_string)FROM(SELECT '\"' AS from_string, '"' AS to_stringUNION ALLSELECT '\\', '\'UNION ALLSELECT '\/', '/'UNION ALLSELECT '\b', CHAR(08)UNION ALLSELECT '\f', CHAR(12)UNION ALLSELECT '\n', CHAR(10)UNION ALLSELECT '\r', CHAR(13)UNION ALLSELECT '\t', CHAR(09)) substitutions;SET @result = 0;SET @escape = 1;/*Begin to take out any hex escape codes*/WHILE @escape > 0BEGIN/* find the next hex escape sequence */SET @index = 0;SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin);IF @escape > 0 /* if there is one */BEGINWHILE @index < 4 /* there are always four digits to a \x sequence */BEGIN/* determine its value */SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1);SET @index = @index + 1;END/* and replace the hex sequence by its unicode value */SET @token = STUFF(@token, @escape, 6, NCHAR(@result));ENDEND/* now store the string away */INSERT INTO @strings (stringvalue) SELECT @token;/* and replace the string with a token */SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity));END/* all strings are now removed. Now we find the first leaf. */WHILE 1 = 1 /* forever until there is nothing more to do */BEGINSET @parent_id = @parent_id + 1;/* find the first object or list by looking for the open bracket */SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin); /*object or array*/IF @firstobject = 0 BREAK;IF (SUBSTRING(@json, @firstobject, 1) = '{')SELECT @nextclosedelimiterChar = '}', @type = 'object';ELSESELECT @nextclosedelimiterChar = ']', @type = 'array';SET @opendelimiter = @firstobject;WHILE 1 = 1 --find the innermost object or list...BEGINSET @lenjson = LEN(@json+'|') - 1;/* find the matching close-delimiter proceeding after the open-delimiter */SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1);/* is there an intervening open-delimiter of either type */SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/IF @nextopendelimiter = 0 BREAK;SET @nextopendelimiter = @nextopendelimiter + @opendelimiter;IF @nextclosedelimiter < @nextopendelimiter BREAK;IF SUBSTRING(@json, @nextopendelimiter, 1) = '{'SELECT @nextclosedelimiterChar = '}', @type = 'object';ELSESELECT @nextclosedelimiterChar = ']', @type = 'array';SET @opendelimiter = @nextopendelimiter;END/* and parse out the list or name/value pairs */SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1);SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id));WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) < > 0BEGIN /* WHILE PATINDEX */IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/BEGINSET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1);SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin);SET @param = RIGHT(@token, LEN(@token)-@endofname + 1);SET @token = LEFT(@token, @endofname - 1);SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1);SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/ENDELSEBEGINSET @name = null;ENDSET @end = CHARINDEX(',', @contents); /*a string-token, object-token, list-token, number,boolean, or null*/IF @end = 0SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1;SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin);/*select @start,@end, LEN(@contents+'|'), @contents */SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start));SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end);IF SUBSTRING(@value, 1, 7) = '@object'INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object';ELSEIF SUBSTRING(@value, 1, 6) = '@array'INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array';ELSEIF SUBSTRING(@value, 1, 7) = '@string'INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) SELECT @name, @parent_id, stringvalue, 'string', 0FROM @strings WHERE string_id = SUBSTRING(@value, 8, 5);ELSEIF @value IN ('true', 'false')INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) SELECT @name, @parent_id, @value, 'boolean', 0;ELSEIF @value = 'null'INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) SELECT @name, @parent_id, @value, 'null', 0;ELSEIF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) SELECT @name, @parent_id, @value, 'real', 0;ELSEINSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue) SELECT @name, @parent_id, @value, 'bigint', 0, dbo.TryConvertBigInt(@value);END /* WHILE PATINDEX */END /* WHILE 1=1 forever until there is nothing more to do */INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)SELECT '', 0, '', @parent_id - 1, @type;RETURN;END GOPRINT 'FUNCTION dbo.ParseJSON is modified.';GO
应用示例1:
Select * from dbo.ParseJSON('{"pair": { "x": 1, "y":2 }"pair": { "x": 3, "y":4 }}');
结果:
应用示例2:
Select * from dbo.ParseJSON('{ "Employee": { "First Name": "Bob", "Last Name": "Smith", "age": 36, "Address": { "street Address":"104 Xiaoyun Road", "city":"Beijing", "Country":"CN", "PostalCode":"100025" }, "PhoneNumbers": { "Mobile":"13684412578", "Work phone":"8610-66772828" } } "Employee": { "First Name": "Alice", "Last Name": "Liu", "age": 25, "Address": { "street Address" : "Stephone street", "city":"Beijing", "Country":"CN", "PostalCode":"100106" }, "PhoneNumbers": { "Mobile":"18685776063", "Work phone":"8610-66990077" } }}');
结果:
- T-SQL Tips: 解析Json字符串
- T-SQL Tips: 通过Json字符串传递一个数组
- T-SQL Tips 1
- T-SQL Tips 2
- Gson解析json字符串,并封装成List<T>
- T-SQL Tips: Convert varchar to bigint
- T-SQL Tips: convert varbinary to varchar
- T-SQL Tips: convert varchar to varbinary
- T-SQL 处理字符串
- T-SQL字符串函数
- json时间字符串带T
- flex解析json字符串
- jackson解析 json字符串
- java 解析 json 字符串
- java解析json字符串
- json解析字符串
- 解析json字符串
- android解析json字符串
- 功能强大的wordpress自定义登录注册插件:DX Login Register 正式发布
- WFP-Windows Filtering Platform Traffic Inspection Sample 启动时候 发生系统错误87
- input file 在不同浏览器中的呈现
- 微信公众帐号开发教程第17篇-应用实例之智能翻译
- kickstart,让linux自动完成安装操作系统
- T-SQL Tips: 解析Json字符串
- Mac OS X 下 php 扩展 memcached 编译安装失败
- 成绩处理--char5
- iPhone7.0.sdk include 文件列表
- java socket 编程经典实例
- 349B - Color the Fence (贪心)
- VS2012下安装OpenCV2.4.6 (WIN7系统)
- 了解iOS中XML解析
- linux修改时区为UTC