SQL解析传入的Json字符串

处理方式：将传入的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 '\b', CHAR(08) 
 68 UNION ALL SELECT '\f', CHAR(12) 
 69 UNION ALL SELECT '\n', CHAR(10) 
 70 UNION ALL SELECT '\r', CHAR(13) 
 71 UNION ALL SELECT '\t', 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
    }
}

 

使用表值函数后，查询到的数据入下：

 

 

 

 

 

posted @ 2019-03-22 11:15  EnjoyToday  阅读(6910)  评论(0)  编辑  收藏  举报