.png)
SQL Server JSON to Table and Table to JSON
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.
And by a quick use of …
We could, of course, have got the table as a Datatable by ADO.NET and converted that to JSON
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.
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.
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.
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+‘", "’+Convert(varchar(10),Availability)+‘", "’+Convert(varchar(10),ModifiedDate,126)+‘"),’
from adventureworks.production.location
@(("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…("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
[
{
"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"
}
]
$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$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()
}
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*/
)
from that point, it is easy to
create the JSON string
(
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’
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’
DECLARE @MyHierarchy JSONHierarchy
INSERT INTO @myHierarchy
SELECT * from #hierarchy
SELECT dbo.ToJSON(@MyHierarchy)
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 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
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.
