Multi-Object JSON Arrays In SQL Server

Kevin Feasel

2017-12-19

JSON

Bert Wagner shows how to build JSON arrays in SQL Server:

When using FOR JSON PATH, ALL rows and columns from that result set will get converted to a single JSON string.

This creates a problem if, for example, you want to have a column for your JSON string and a separate column for something like a foreign key (in our case, HomeId). Or if you want to generate multiple JSON strings filtered on a foreign key.

The way I chose to get around this is to use CROSS APPLY with a join back to our Home table — this way we get our JSON string for either Cars or Toys created but then output it along with some additional columns.

Impedance mismatch?  What impedance mismatch?

Related Posts

Using JSON_MODIFY To Modify Existing JSON

Jovan Popovic shows off the JSON_MODIFY function in SQL Server:   Recently I found this question on stack overflow. The problem was in appending a new JSON object to the existing JSON array: UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}') WHERE Condition = 1; JSON_MODIFY function should take the array value […]

Read More

DataRow To JSON With Powershell

Rob Sewell shows how to convert a .NET DataRow into its JSON form using Powershell: I wanted to be able to Mock $variable. I wrapped the code above in a function, let’s call it Run-Query 1 2 3 4 function Run-Query {(Param $query) $db = Get-DbaDatabase -SqlInstance $Instance -Database $Database $variable = $db.Query($Query) } Which […]

Read More

Categories

December 2017
MTWTFSS
« Nov Jan »
 123
45678910
11121314151617
18192021222324
25262728293031