Recursive CTE for parsing JSON file

Question

I'm using SQL Server 2014 for parsing a JSON file and for that I think that a recursive CTE is convenient.

The JSON file is like this:

{
  "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
  }

}

Can anyone help me with this?

Could you perhaps provide your required output schema? Is it Key,Value? Also, please indicate if the code needs to perform exclusively on this JSON schema, or on a variety. — MarkD, Dec 04 '17 at 09:12
Possible duplicate of [Parse JSON in TSQL](https://stackoverflow.com/questions/2867501/parse-json-in-tsql) — MarkD, Dec 04 '17 at 10:07

score 0 · Answer 1 · answered Dec 04 '17 at 18:32

You don't need recursion for this. Grab a copy of delimitedSplit8K and do this:

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;

Results

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

Recursive CTE for parsing JSON file

1 Answers1