处理方式：将传入的Json字符串保存到表值函数中，读取表值函数中的数据
表值函数建立如下：
1 CREATE FUNCTION [dbo].[parseJSON] 2 ( 3 @Json NVARCHAR(MAX) 4 ) 5 RETURNS @hierarchy TABLE 6 ( 7 element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ 8 sequenceNo [int] NULL, /* the place in the sequence for the element */ 9 parent_ID INT,/* 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 */ 10 Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 11 NAME NVARCHAR(2000),/* the name of the object */ 12 StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */ 13 ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/ 14 ) 15 AS 16 BEGIN 17 18 DECLARE 19 @FirstObject INT, --the index of the first open bracket found in the JSON string 20 @OpenDelimiter INT,--the index of the next open bracket found in the JSON string 21 @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string 22 @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string 23 @Type NVARCHAR(10),--whether it denotes an object or an array 24 @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']' 25 @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression 26 @Start INT, --index of the start of the token that you are parsing 27 @end INT,--index of the end of the token that you are parsing 28 @param INT,--the parameter at the end of the next Object/Array token 29 @EndOfName INT,--the index of the start of the parameter at end of Object/Array token 30 @token NVARCHAR(200),--either a string or object 31 @value NVARCHAR(MAX), -- the value as a string 32 @SequenceNo int, -- the sequence number within a list 33 @name NVARCHAR(200), --the name as a string 34 @parent_ID INT,--the next parent ID to allocate 35 @lenJSON INT,--the current length of the JSON String 36 @characters NCHAR(36),--used to convert hex to decimal 37 @result BIGINT,--the value of the hex symbol being parsed 38 @index SMALLINT,--used for parsing the hex value 39 @Escape INT --the index of the next escape character 40 DECLARE @Strings TABLE /* 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 */ 41 ( 42 String_ID INT IDENTITY(1, 1),StringValue NVARCHAR(MAX) 43 ) 44 SELECT--initialise the characters to convert hex to ascii 45 @characters='0123456789abcdefghijklmnopqrstuvwxyz', 46 @SequenceNo=0, --set the sequence no. to something sensible. 47 @parent_ID=0; 48 WHILE 1=1 --forever until there is nothing more to do 49 BEGIN 50 SELECT 51 @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string 52 IF @start=0 BREAK --no more so drop through the WHILE loop 53 IF SUBSTRING(@json, @start+1, 1)='"' 54 BEGIN --Delimited Name 55 SET @start=@Start+1; 56 SET @end=PATINDEX('%[^]["]%', RIGHT(@json, LEN(@json+'|')-@start)); 57 END 58 IF @end=0 --no end delimiter to last string 59 BREAK --no more 60 SELECT @token=SUBSTRING(@json, @start+1, @end-1) 61 SELECT @token=REPLACE(@token, FROMString, TOString) 62 FROM 63 (SELECT 64 '"' AS FromString, '"' AS ToString 65 UNION ALL SELECT '\', '' 66 UNION ALL SELECT '/', '/' 67 UNION ALL SELECT '', CHAR(08) 68 UNION ALL SELECT 'f', CHAR(12) 69 UNION ALL SELECT ' ', CHAR(10) 70 UNION ALL SELECT ' ', CHAR(13) 71 UNION ALL SELECT ' ', CHAR(09) 72 UNION ALL SELECT '゛', CHAR(09) 73 ) substitutions 74 SELECT @result=0,@escape=1 75 WHILE @escape>0 76 BEGIN 77 SELECT @index=0, 78 @escape=PATINDEX('%x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token) 79 IF @escape>0 --if there is one 80 BEGIN 81 WHILE @index<4 --there are always four digits to a x sequence 82 BEGIN 83 SELECT --determine its value 84 @result=@result+POWER(16, @index) 85 *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1), 86 @characters)-1), @index=@index+1 ; 87 END 88 SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result)) 89 END 90 END 91 INSERT INTO @Strings (StringValue) SELECT @token 92 SELECT @JSON=STUFF(@json, @start, @end+1,'@string'+CONVERT(NVARCHAR(5), @@identity)) 93 END 94 WHILE 1=1 --forever until there is nothing more to do 95 BEGIN 96 SELECT @parent_ID=@parent_ID+1 97 SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array 98 IF @FirstObject = 0 BREAK 99 IF (SUBSTRING(@json, @FirstObject, 1)='{') 100 SELECT @NextCloseDelimiterChar='}', @type='object' 101 ELSE 102 SELECT @NextCloseDelimiterChar=']', @type='array' 103 SELECT @OpenDelimiter=@firstObject 104 WHILE 1=1 --find the innermost object or list... 105 BEGIN 106 SELECT 107 @lenJSON=LEN(@JSON+'|')-1 108 SELECT 109 @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar,@json,@OpenDelimiter+1) 110 SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', 111 RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object 112 IF @NextOpenDelimiter=0 113 BREAK 114 SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter 115 IF @NextCloseDelimiter<@NextOpenDelimiter 116 BREAK 117 IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{' 118 SELECT @NextCloseDelimiterChar='}', @type='object' 119 ELSE 120 SELECT @NextCloseDelimiterChar=']', @type='array' 121 SELECT @OpenDelimiter=@NextOpenDelimiter 122 END 123 SELECT 124 @contents=SUBSTRING(@json, @OpenDelimiter+1,@NextCloseDelimiter-@OpenDelimiter-1) 125 SELECT 126 @JSON=STUFF(@json, @OpenDelimiter,@NextCloseDelimiter-@OpenDelimiter+1,'@'+@type+CONVERT(NVARCHAR(5), @parent_ID)) 127 WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0 128 BEGIN 129 IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null 130 BEGIN 131 SELECT 132 @SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name. 133 SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents)--AAAAAAAA 134 SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1), 135 @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin), 136 @param=RIGHT(@token, LEN(@token)-@endofname+1) 137 SELECT 138 @token=LEFT(@token, @endofname-1), 139 @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1) 140 SELECT @name=stringvalue FROM @strings 141 WHERE string_id=@param --fetch the name 142 END 143 ELSE 144 SELECT @Name=null,@SequenceNo=@SequenceNo+1 145 SELECT 146 @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null 147 IF @end=0 148 SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ') +1 149 SELECT 150 @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents) 151 --select @start,@end, LEN(@contents+'|'), @contents 152 SELECT 153 @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)), 154 @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end) 155 IF SUBSTRING(@value, 1, 7)='@object' 156 INSERT INTO @hierarchy 157 (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) 158 SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5), 159 SUBSTRING(@value, 8, 5), 'object' 160 ELSE 161 IF SUBSTRING(@value, 1, 6)='@array' 162 INSERT INTO @hierarchy 163 (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) 164 SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5), 165 SUBSTRING(@value, 7, 5), 'array' 166 ELSE 167 IF SUBSTRING(@value, 1, 7)='@string' 168 INSERT INTO @hierarchy 169 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 170 SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string' 171 FROM @strings 172 WHERE string_id=SUBSTRING(@value, 8, 5) 173 ELSE 174 IF @value IN ('true', 'false') 175 INSERT INTO @hierarchy 176 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 177 SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean' 178 ELSE 179 IF @value='null' 180 INSERT INTO @hierarchy 181 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 182 SELECT @name, @SequenceNo, @parent_ID, @value, 'null' 183 ELSE 184 IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0 185 INSERT INTO @hierarchy 186 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 187 SELECT @name, @SequenceNo, @parent_ID, @value, 'real' 188 ELSE 189 INSERT INTO @hierarchy 190 (NAME, SequenceNo, parent_ID, StringValue, ValueType) 191 SELECT @name, @SequenceNo, @parent_ID, @value, 'int' 192 193 if @Contents=' ' Select @SequenceNo=0 194 END 195 END 196 RETURN 197 END
测试如下：
传入的Json字符串如下：
{
"data":{
"order_sn":"liaoning19032114523809671",
"created_at":1553152116,
"storeno":"SYS20170209014",
"goods_amount":"3880.00",
"order_amount":"3880.00",
"points":"0.00",
"pmt_amount":"64.20",
"suppliers_name":"盘锦爱心电脑科技有限公司",
"products":[
{
"sn":"90NB0E42-M02110",
"number":"2",
"price":"1940.00"
}
],
"coupon":[
{
"coupon_code":"As20190314140336018"
},
{
"coupon_code":"As20190314140443050"
}
],
"user_id":5284
}
}
使用表值函数后，查询到的数据入下：
