Click here to monitor SSC

Simple-Talk columnist

SQL Server JSON to Table and Table to JSON

Published 26 March 2013 4:50 pm

One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server.  I hadn’t really expected it to be so appreciated; in fact I was nervous about posting it at all. It came from a real requirement I had at the time, but I got interested in it in order to show how one could analyse hierarchical data documents iteratively in TSQL. Also, an anonymous troll on StackOverflow had told me it was impossible.

There were a few questions that I’d left unanswered. The first was how to read a JSON string as a table, and the other was how to produce a JSON document from a table. I ran out of space in the article and thought it was really the sort of thing that readers of the article would want to experiment with.

In this blog, I’ll take the whole process around the circle.  You’ll notice that much of this is a bit cumbersome, but I only use short JSON Strings and haven’t noticed a performance problem in doing this.

We start with a table.

Select *  from adventureworks.production.location

And by a quick use of …

  Select ‘("’+Convert(varchar(3),LocationID)+‘", "’+ Name+‘", "’+Convert(varchar(10),CostRate)
   
+‘", "’+Convert(varchar(10),Availability)+‘", "’+Convert(varchar(10),ModifiedDate,126)+‘"),’

  from
adventureworks.production.location

We can convert it to a PowerShell array that we can use as if it were a table

@(("1", "Tool Crib", "0.00", "0.00", "2002-06-01"),
("2", "Sheet Metal Racks", "0.00", "0.00", "2002-06-01"),
("3", "Paint Shop", "0.00", "0.00", "2002-06-01"),
("4", "Paint Storage", "0.00", "0.00", "2002-06-01"),
("5", "Metal Storage", "0.00", "0.00", "2002-06-01"),
("6", "Miscellaneous Storage", "0.00", "0.00", "2002-06-01"),
("7", "Finished Goods Storage", "0.00", "0.00", "2002-06-01"),
("10", "Frame Forming", "22.50", "96.00", "2002-06-01"),
("20", "Frame Welding", "25.00", "108.00", "2002-06-01"),
("30", "Debur and Polish", "14.50", "120.00", "2002-06-01"),
("40", "Paint", "15.75", "120.00", "2002-06-01"),
("45", "Specialized Paint", "18.00", "80.00", "2002-06-01"),
("50", "Subassembly", "12.25", "120.00", "2002-06-01"),
("60", "Final Assembly", "12.25", "120.00", "2002-06-01")
)|
   Select @{name="LocationID"; Expression = {$_[0]}},
                     @{name="Name"; Expression = {$_[1]}},
                     @{name="CostRate"; Expression = {$_[2]}},
                     @{name="Availability"; Expression = {$_[3]}},
                     @{name="ModifiedDate"; Expression = {(Get-Date $_[4]).DateTime}} |
     convertTo-json

… and this we can use to get the JSON version of the SQL Table, and from there to JSON…

[
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Tool Crib",
        "Availability":  "0.00",
        "LocationID":  "1"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Sheet Metal Racks",
        "Availability":  "0.00",
        "LocationID":  "2"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Paint Shop",
        "Availability":  "0.00",
        "LocationID":  "3"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Paint Storage",
        "Availability":  "0.00",
        "LocationID":  "4"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Metal Storage",
        "Availability":  "0.00",
        "LocationID":  "5"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Miscellaneous Storage",
        "Availability":  "0.00",
        "LocationID":  "6"
    },
    {
        "CostRate":  "0.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Finished Goods Storage",
        "Availability":  "0.00",
        "LocationID":  "7"
    },
    {
        "CostRate":  "22.5000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Frame Forming",
        "Availability":  "96.00",
        "LocationID":  "10"
    },
    {
        "CostRate":  "25.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Frame Welding",
        "Availability":  "108.00",
        "LocationID":  "20"
    },
    {
        "CostRate":  "14.5000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Debur and Polish",
        "Availability":  "120.00",
        "LocationID":  "30"
    },
    {
        "CostRate":  "15.7500",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Paint",
        "Availability":  "120.00",
        "LocationID":  "40"
    },
    {
        "CostRate":  "18.0000",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Specialized Paint",
        "Availability":  "80.00",
        "LocationID":  "45"
    },
    {
        "CostRate":  "12.2500",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Subassembly",
        "Availability":  "120.00",
        "LocationID":  "50"
    },
    {
        "CostRate":  "12.2500",
        "ModifiedDate":  "06/01/1998 00:00:00",
        "Name":  "Final Assembly",
        "Availability":  "120.00",
        "LocationID":  "60"
    }
]

We could, of course, have got the table as a Datatable by ADO.NET and converted that to JSON

$SourceTable=‘production.location’
$Sourceinstance=‘MyInstanceName’
$Sourcedatabase=‘Adventureworks’

$SourceConnectionString = "Data Source=$Sourceinstance;Initial Catalog=$Sourcedatabase;Integrated Security=True"
$sql = "select * FROM $SourceTable"
$result=@()
try
{
       $sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString)
       $sourceConnection.open()
       $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
       $reader = $commandSourceData.ExecuteReader()
       $Counter = $Reader.FieldCount
   while ($Reader.Read()) {
              $tuple=@{}
              for ($i = 0; $i -lt $Counter; $i++) {
#$Reader.GetFieldType($i).Name
                      $tuple."$($Reader.GetName($i))"="$(if ($Reader.GetFieldType($i).Name -eq ‘DateTime’)
                                                  {$Reader.GetDateTime($i)}
                                             else {$Reader.GetValue($i)})";
                 }
              $Result+=$tuple
      }
  $result |convertTo-JSON
}
catch
{
    $ex = $_.Exception
    Write-Error "whilst opening source $Sourceinstance . $Sourcedatabase . $SourceTable : $ex.Message"
}
finally
{
    $reader.close()
}

Which produces the same JSON String

To produce this directly from the table takes more effort. First you would need to install the library from my JSON article. You can convert the table to a hierarchy table.

create TABLE #hierarchy
  (
   element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
   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 */
   Object_ID INT,/* 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(MAX) NOT NULL,/*the string representation of the value of the element. */
   ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
  )

;With loc (Roworder,locationID, Name, CostRate, Availability, ModifiedDate)
as
(
Select  ROW_NUMBER() OVER ( ORDER BY locationID) as RowOrder,
       LocationID, Name, CostRate, Availability, ModifiedDate
from Adventureworks.production.location
)
 
INSERT INTO #Hierarchy (parent_ID,Object_ID,NAME,StringValue,ValueType)
          Select Roworder,null,‘LocationID’, convert(varchar(5),LocationID),‘int’  from loc
union all Select Roworder,null,‘Name’, Name ,‘string’  from  loc
union all Select Roworder,null,‘CostRate’, convert(varchar(10),CostRate) ,‘real’  from  loc
union all Select Roworder,null,‘Availability’, convert(varchar(10),Availability) ,‘real’  from  loc
union all Select Roworder,null,‘ModifiedDate’, Convert(varchar(10),ModifiedDate,126) ,‘string’  from  loc
union all Select (Select count(*) from loc)+1, ROW_NUMBER() OVER ( ORDER BY locationID ), NULL,’1′,‘object’ from  loc
union all Select null, (Select count(*) from loc)+1,‘-’,,‘array’

from that point, it is easy to create the JSON string

 
DECLARE @MyHierarchy JSONHierarchy
INSERT INTO @myHierarchy
       SELECT * from #hierarchy
 
SELECT dbo.ToJSON(@MyHierarchy)

And it won’t surprise you that the string is more or less identical to the one we got in PowerShell.

Of course, reading a JSON string as a table is rather easier.

Create view TableOfJSONString as
 
Select parent_ID,
       max(case when name=‘LocationID’ then convert(int,StringValue) else 0 end) as LocationID,
       max(case when name=‘Name’ then convert(Varchar(50),StringValue) else end) as Name,
       max(case when name=‘CostRate’ then convert(SmallMoney,StringValue) else 0 end) as CostRate,
       max(case when name=‘Availability’ then convert(Decimal(8,2),StringValue) else 0  end) as Availability,
       max(case when name=‘ModifiedDate’ then convert(DateTime,StringValue) else 0 end) as ModifiedDate
from dbo.parseJSON( ‘[
    {
        "LocationID":  "1",
        "Name":  "Tool Crib",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "2",
        "Name":  "Sheet Metal Racks",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "3",
        "Name":  "Paint Shop",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "4",
        "Name":  "Paint Storage",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "5",
        "Name":  "Metal Storage",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "6",
        "Name":  "Miscellaneous Storage",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "7",
        "Name":  "Finished Goods Storage",
        "CostRate":  "0.00",
        "Availability":  "0.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "10",
        "Name":  "Frame Forming",
        "CostRate":  "22.50",
        "Availability":  "96.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "20",
        "Name":  "Frame Welding",
        "CostRate":  "25.00",
        "Availability":  "108.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "30",
        "Name":  "Debur and Polish",
        "CostRate":  "14.50",
        "Availability":  "120.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "40",
        "Name":  "Paint",
        "CostRate":  "15.75",
        "Availability":  "120.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "45",
        "Name":  "Specialized Paint",
        "CostRate":  "18.00",
        "Availability":  "80.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "50",
        "Name":  "Subassembly",
        "CostRate":  "12.25",
        "Availability":  "120.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    },
    {
        "LocationID":  "60",
        "Name":  "Final Assembly",
        "CostRate":  "12.25",
        "Availability":  "120.00",
        "ModifiedDate":  "01 June 2002 00:00:00"
    }
]

  )
where ValueType = ‘string’
group by parent_ID

And we can just then use it in any SQL Expression like this

Select * from TableOfJSONString

You’ll notice that I’ve given you a ‘parent_ID’ to give you the intrinsic order of the rows, since these things can be significant in a JSON document.

Of course, you can do some dynamic SQL to deal with any JSON String, but I don’t like to do this since there is no guarantee that a JSON string represents table data. Also, when you are getting json strings from an application repeatedly, they tend to  carry the same metadata.

Leave a Reply