{"id":5420,"date":"2013-03-26T16:50:17","date_gmt":"2013-03-26T16:50:17","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-json-to-table-and-table-to-json\/"},"modified":"2019-01-23T16:15:50","modified_gmt":"2019-01-23T16:15:50","slug":"sql-server-json-to-table-and-table-to-json","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-json-to-table-and-table-to-json\/","title":{"rendered":"SQL Server JSON to Table and Table to JSON"},"content":{"rendered":"

Articles by Phil Factor about JSON and SQL Server:<\/h4>\n
    \n
  1. Consuming JSON Strings in SQL Server (Nov 2012)<\/a> <\/li>\n
  2. SQL Server JSON to Table and Table to JSON (March 2013)<\/a> <\/li>\n
  3. Producing JSON Documents From SQL Server Queries via TSQL (May 2014)<\/a> <\/li>\n
  4. Consuming hierarchical JSON documents in SQL Server using OpenJSON (Sept 2017)<\/a> <\/li>\n
  5. Importing JSON data from Web Services and Applications into SQL Server(October 2017)<\/a> <\/li>\n<\/ol>\n\n

    One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server<\/a>.\u00a0 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.<\/p>\n

    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.<\/p>\n

    In this blog, I’ll take the whole process around the circle.\u00a0 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.<\/p>\n

    We start with a table.<\/p>\n

    Select *\u00a0from adventureworks.production.location\r\n<\/pre>\n
    \"StartTable.jpg\"<\/p>\n
    And by a quick use of …<\/p>\n
        Select '(\"'+Convert(varchar(3),LocationID)+'\", \"'+ Name+'\", \"'+Convert(varchar(10),CostRate)\r\n         \u00a0\u00a0\u00a0 +'\", \"'+Convert(varchar(10),Availability)+'\", \"'+Convert(varchar(10),ModifiedDate,126)+'\"),'\r\n         \u00a0 from adventureworks.production.location\r\n<\/pre>\n
    We can convert it to a PowerShell array that we can use as if it were a table<\/p>\n
    @((\"1\", \"Tool Crib\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"2\", \"Sheet Metal Racks\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"3\", \"Paint Shop\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"4\", \"Paint Storage\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"5\", \"Metal Storage\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"6\", \"Miscellaneous Storage\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"7\", \"Finished Goods Storage\", \"0.00\", \"0.00\", \"2002-06-01\"),\r\n (\"10\", \"Frame Forming\", \"22.50\", \"96.00\", \"2002-06-01\"),\r\n (\"20\", \"Frame Welding\", \"25.00\", \"108.00\", \"2002-06-01\"),\r\n (\"30\", \"Debur and Polish\", \"14.50\", \"120.00\", \"2002-06-01\"),\r\n (\"40\", \"Paint\", \"15.75\", \"120.00\", \"2002-06-01\"),\r\n (\"45\", \"Specialized Paint\", \"18.00\", \"80.00\", \"2002-06-01\"),\r\n (\"50\", \"Subassembly\", \"12.25\", \"120.00\", \"2002-06-01\"),\r\n (\"60\", \"Final Assembly\", \"12.25\", \"120.00\", \"2002-06-01\")\r\n) |\r\nSelect @{ name = \"LocationID\"; Expression = { $_[0] } },\r\n @{ name = \"Name\"; Expression = { $_[1] } },\r\n @{ name = \"CostRate\"; Expression = { $_[2] } },\r\n @{ name = \"Availability\"; Expression = { $_[3] } },\r\n @{ name = \"ModifiedDate\"; Expression = { (Get-Date $_[4]).DateTime } } |\r\nconvertTo-json\r\n<\/pre>\n
    … and this we can use to get the JSON version of the SQL Table, and from there to JSON…<\/p>\n
    \n
    \r\n[\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Tool Crib\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"1\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Sheet Metal Racks\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"2\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Paint Shop\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"3\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Paint Storage\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"4\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Metal Storage\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"5\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Miscellaneous Storage\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"6\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"0.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Finished Goods Storage\",\r\n        \"Availability\":  \"0.00\",\r\n        \"LocationID\":  \"7\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"22.5000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Frame Forming\",\r\n        \"Availability\":  \"96.00\",\r\n        \"LocationID\":  \"10\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"25.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Frame Welding\",\r\n        \"Availability\":  \"108.00\",\r\n        \"LocationID\":  \"20\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"14.5000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Debur and Polish\",\r\n        \"Availability\":  \"120.00\",\r\n        \"LocationID\":  \"30\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"15.7500\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Paint\",\r\n        \"Availability\":  \"120.00\",\r\n        \"LocationID\":  \"40\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"18.0000\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Specialized Paint\",\r\n        \"Availability\":  \"80.00\",\r\n        \"LocationID\":  \"45\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"12.2500\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Subassembly\",\r\n        \"Availability\":  \"120.00\",\r\n        \"LocationID\":  \"50\"\r\n    },\r\n    {\r\n        \"CostRate\":  \"12.2500\",\r\n        \"ModifiedDate\":  \"06\/01\/1998 00:00:00\",\r\n        \"Name\":  \"Final Assembly\",\r\n        \"Availability\":  \"120.00\",\r\n        \"LocationID\":  \"60\"\r\n    }\r\n]<\/code><\/pre>\n<\/div>\n
    We could, of course, have got the table as a Datatable by ADO.NET and converted that to JSON<\/p>\n
    $SourceTable = 'production.location'\r\n$Sourceinstance = 'MyInstanceName'\r\n$Sourcedatabase = 'Adventureworks'\r\n\r\n$SourceConnectionString = \"Data Source=$Sourceinstance;Initial Catalog=$Sourcedatabase;Integrated Security=True\"\r\n$sql = \"select * FROM $SourceTable\"\r\n$result = @()\r\ntry\r\n{\r\n    $sourceConnection = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString)\r\n    $sourceConnection.open()\r\n    $commandSourceData = New-Object system.Data.SqlClient.SqlCommand($sql, $sourceConnection)\r\n    $reader = $commandSourceData.ExecuteReader()\r\n    $Counter = $Reader.FieldCount\r\n    while ($Reader.Read())\r\n    {\r\n        $tuple = @{ }\r\n        for ($i = 0; $i -lt $Counter; $i++)\r\n        {\r\n            $tuple.\"$($Reader.GetName($i))\" = \"$(if ($Reader.GetFieldType($i).Name -eq 'DateTime')\r\n                { $Reader.GetDateTime($i) }\r\n                else { $Reader.GetValue($i) })\";\r\n        }\r\n        $Result += $tuple\r\n    }\r\n    $result | convertTo-JSON\r\n}\r\ncatch\r\n{\r\n    $ex = $_.Exception\r\n    Write-Error \"whilst opening source $Sourceinstance . $Sourcedatabase . $SourceTable : $ex.Message\"\r\n}\r\nfinally\r\n{\r\n    $reader.close()\r\n}<\/pre>\n
    Which produces the same JSON String. This is just a simple example: \u00a0This will not cope with a Varbinary \u00a0binary, image, timestamp or rowversion field. \u00a0You would need to convert it to a Base64 string \u00a0via\u00a0[System.Convert]::ToBase64String($Reader.GetValue($i))<\/strong>.<\/p>\n
    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.<\/p>\n
        create TABLE #hierarchy\r\n        \u00a0 (\r\n        \u00a0\u00a0 element_id INT IDENTITY(1, 1) NOT NULL, \/* internal surrogate primary key gives the order of parsing and the list order *\/\r\n        \u00a0\u00a0 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 *\/\r\n        \u00a0\u00a0 Object_ID INT,\/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here *\/\r\n        \u00a0\u00a0 NAME NVARCHAR(2000),\/* the name of the object *\/\r\n        \u00a0\u00a0 StringValue NVARCHAR(MAX) NOT NULL,\/*the string representation of the value of the element. *\/\r\n        \u00a0\u00a0 ValueType VARCHAR(10) NOT null \/* the declared type of the value represented as a string in StringValue*\/\r\n        \u00a0 )\r\n        \r\n    \r\n     ;With loc (Roworder,locationID, Name, CostRate, Availability, ModifiedDate)\r\n        as\r\n        (\r\n        Select\u00a0 ROW_NUMBER() OVER ( ORDER BY locationID) as RowOrder,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LocationID, Name, CostRate, Availability, ModifiedDate\r\n        from Adventureworks.production.location\r\n        )\r\n        \r\n        INSERT INTO #Hierarchy (parent_ID,Object_ID,NAME,StringValue,ValueType)\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select Roworder,null,'LocationID', convert(varchar(5),LocationID),'int'\u00a0 from loc\r\n        union all Select Roworder,null,'Name', Name ,'string'\u00a0 from\u00a0 loc\r\n        union all Select Roworder,null,'CostRate', convert(varchar(10),CostRate) ,'real'\u00a0 from\u00a0 loc\r\n        union all Select Roworder,null,'Availability', convert(varchar(10),Availability) ,'real'\u00a0 from\u00a0 loc\r\n        union all Select Roworder,null,'ModifiedDate', Convert(varchar(10),ModifiedDate,126) ,'string'\u00a0 from\u00a0 loc\r\n        union all Select (Select count(*) from loc)+1, ROW_NUMBER() OVER ( ORDER BY locationID ), NULL,'1','object' from\u00a0 loc\r\n        union all Select null, (Select count(*) from loc)+1,'-','','array'\r\n<\/pre>\n
    from that point, it is easy to create the JSON string<\/p>\n
        DECLARE @MyHierarchy JSONHierarchy\r\n    INSERT INTO @myHierarchy\r\n    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * from #hierarchy\r\n    \r\n    SELECT dbo.ToJSON(@MyHierarchy)\r\n<\/pre>\n
    And it won’t surprise you that the string is more or less identical to the one we got in PowerShell.<\/p>\n
    Of course, reading a JSON string as a table is rather easier.<\/p>\n
        Create view TableOfJSONString as\r\n        \r\n        Select parent_ID,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(case when name='LocationID' then convert(int,StringValue) else 0 end) as LocationID,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(case when name='Name' then convert(Varchar(50),StringValue) else '' end) as Name,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(case when name='CostRate' then convert(SmallMoney,StringValue) else 0 end) as CostRate,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(case when name='Availability' then convert(Decimal(8,2),StringValue) else 0\u00a0 end) as Availability,\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(case when name='ModifiedDate' then convert(DateTime,StringValue) else 0 end) as ModifiedDate\r\n        from dbo.parseJSON( '[\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"1\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Tool Crib\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"2\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Sheet Metal Racks\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"3\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Paint Shop\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"4\",\r\n        \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\"Name\":\u00a0 \"Paint Storage\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"5\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Metal Storage\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"6\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Miscellaneous Storage\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"7\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Finished Goods Storage\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"0.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"10\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Frame Forming\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"22.50\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"96.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"20\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Frame Welding\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"25.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"108.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"30\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Debur and Polish\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"14.50\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"120.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"40\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Paint\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"15.75\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"120.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"45\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Specialized Paint\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"18.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"80.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"50\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Subassembly\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"12.25\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"120.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 },\r\n        \u00a0\u00a0\u00a0 {\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"LocationID\":\u00a0 \"60\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Name\":\u00a0 \"Final Assembly\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"CostRate\":\u00a0 \"12.25\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Availability\":\u00a0 \"120.00\",\r\n        \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ModifiedDate\":\u00a0 \"01 June 2002 00:00:00\"\r\n        \u00a0\u00a0\u00a0 }\r\n        ]\r\n        '\r\n        \u00a0 )\r\n        where ValueType = 'string'\r\n        group by parent_ID\r\n<\/pre>\n
    And we can just then use it in any SQL Expression like this<\/p>\n
        Select * from TableOfJSONString\r\n<\/pre>\n
    \"EndTable.jpg\"<\/p>\n
    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.<\/p>\n
    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\u00a0 carry the same metadata.<\/p>\n","protected":false},"excerpt":{"rendered":"
    One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server.\u00a0 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……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5134],"coauthors":[6813],"class_list":["post-5420","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=5420"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5420\/revisions"}],"predecessor-version":[{"id":72645,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/5420\/revisions\/72645"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=5420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=5420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=5420"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=5420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}