• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

用于解析JSON文件的递归CTE

sql 来源:hamphilos 4次浏览

我正在使用SQL Server 2014解析JSON文件,为此我认为递归CTE很方便。用于解析JSON文件的递归CTE

的JSON文件是这样的:

{ 
    "0": { 
    "SalesOrderNumber": "CSVSO67695", 
    "SalesOrderDetailID": 97971, 
    "OrderDate": "2014-03-05 00:00:00.000", 
    "ProductNumber": "WB-H098", 
    "Quantity": 1, 
    "LineTotal": 4.99, 
    "CustomerType": "Individual", 
    "TestData_1": "Sales extract OK!", 
    "TestData_2": 255 
    }, 
    "1": { 
    "SalesOrderNumber": "CSVSO53485", 
    "SalesOrderDetailID": 47747, 
    "OrderDate": "2013-07-31 00:00:00.000", 
    "ProductNumber": "SJ-0194-L", 
    "Quantity": 10, 
    "LineTotal": 323.94, 
    "CustomerType": "Store", 
    "TestData_1": "Sales extract OK!", 
    "TestData_2": 255 
    }, 
    "2": { 
    "SalesOrderNumber": "CSVSO52248", 
    "SalesOrderDetailID": 43809, 
    "OrderDate": "2013-07-07 00:00:00.000", 
    "ProductNumber": "TT-M928", 
    "Quantity": 1, 
    "LineTotal": 4.99, 
    "CustomerType": "Individual", 
    "TestData_1": "Sales extract OK!", 
    "TestData_2": 255 
    } 

}

谁能帮助我?


===========解决方案如下:

你不需要为此递归。抢delimitedSplit8K副本,并做到这一点:

declare @json varchar(8000) = 
'{ 
"0": { 
"SalesOrderNumber": "CSVSO67695", 
"SalesOrderDetailID": 97971, 
"OrderDate": "2014-03-05 00:00:00.000", 
"ProductNumber": "WB-H098", 
"Quantity": 1, 
"LineTotal": 4.99, 
"CustomerType": "Individual", 
"TestData_1": "Sales extract OK!", 
"TestData_2": 255 
}, 
"1": { 
"SalesOrderNumber": "CSVSO53485", 
"SalesOrderDetailID": 47747, 
"OrderDate": "2013-07-31 00:00:00.000", 
"ProductNumber": "SJ-0194-L", 
"Quantity": 10, 
"LineTotal": 323.94, 
"CustomerType": "Store", 
"TestData_1": "Sales extract OK!", 
"TestData_2": 255 
}, 
"2": { 
"SalesOrderNumber": "CSVSO52248", 
"SalesOrderDetailID": 43809, 
"OrderDate": "2013-07-07 00:00:00.000", 
"ProductNumber": "TT-M928", 
"Quantity": 1, 
"LineTotal": 4.99, 
"CustomerType": "Individual", 
"TestData_1": "Sales extract OK!", 
"TestData_2": 255 
    } 
}'; 

select 
    jsonItemId, 
    item, 
    attrib, 
    attribValue 
from 
(
    select 
    jsonItemId = sum(sign(jsonItemIdPrep)) over (order by itemnumber), 
    ItemNumber, 
    item, 
    attrib  = ltrim(replace(substring(item, 1, sep.pos-1),'"','')), 
    attribValue = replace(ltrim(replace(substring(item, sep.pos+1, len(item)),'"','')),',',''), 
    jsonItemIdPrep 
    from 
    (
    select ItemNumber, item, jsonItemIdPrep = 
     case 
     when item like '%"[0-9]": {%' 
      or item like '%"[0-9][0-9]": {%' 
      or item like '%"[0-9][0-9][0-9]": {%' -- 1,2 or 3 digits 
     then substring(item, v.ps, charindex('"', item, v.ps)- v.ps) 
     end 
    from dbo.DelimitedSplit8K(replace(@json,char(10),''), char(13)) 
    cross apply (values (charindex('"',item)+1)) v(ps) 
    where ItemNumber > 1 and item not like '%}' and item not like '%},' 
) x 
    cross apply (values (charindex(':', item))) sep(pos) 
    cross apply (values (replace(substring(item, 1, sep.pos-1),'"',''))) p(xxx) 
) x 
where jsonItemIdPrep is null; 

结果

jsonItemId item             attrib      attribValue 
----------- ----------------------------------------------------- ----------------------------- ----------------------------------- 
0   "SalesOrderNumber": "CSVSO67695",     SalesOrderNumber    CSVSO67695 
0   "SalesOrderDetailID": 97971,      SalesOrderDetailID   97971 
0   "OrderDate": "2014-03-05 00:00:00.000",   OrderDate      2014-03-05 00:00:00.000 
0   "ProductNumber": "WB-H098",      ProductNumber     WB-H098 
0   "Quantity": 1,         Quantity      1 
0   "LineTotal": 4.99,        LineTotal      4.99 
0   "CustomerType": "Individual",      CustomerType     Individual 
0   "TestData_1": "Sales extract OK!",    TestData_1     Sales extract OK! 
0   "TestData_2": 255         TestData_2     255 
1   "SalesOrderNumber": "CSVSO53485",     SalesOrderNumber    CSVSO53485 
1   "SalesOrderDetailID": 47747,      SalesOrderDetailID   47747 
1   "OrderDate": "2013-07-31 00:00:00.000",   OrderDate      2013-07-31 00:00:00.000 
1   "ProductNumber": "SJ-0194-L",      ProductNumber     SJ-0194-L 
1   "Quantity": 10,         Quantity      10 
1   "LineTotal": 323.94,        LineTotal      323.94 
1   "CustomerType": "Store",       CustomerType     Store 
1   "TestData_1": "Sales extract OK!",    TestData_1     Sales extract OK! 
1   "TestData_2": 255         TestData_2     255 
2   "SalesOrderNumber": "CSVSO52248",     SalesOrderNumber    CSVSO52248 
2   "SalesOrderDetailID": 43809,      SalesOrderDetailID   43809 
2   "OrderDate": "2013-07-07 00:00:00.000",   OrderDate      2013-07-07 00:00:00.000 
2   "ProductNumber": "TT-M928",      ProductNumber     TT-M928 
2   "Quantity": 1,         Quantity      1 
2   "LineTotal": 4.99,        LineTotal      4.99 
2   "CustomerType": "Individual",      CustomerType     Individual 
2   "TestData_1": "Sales extract OK!",    TestData_1     Sales extract OK! 
2   "TestData_2": 255         TestData_2     255 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)