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

Azure SQL Managed Instance Inventory Analysis

Kevin Feasel

2018-10-03

Cloud, JSON

Jovan Popovic shows us how to use the Azure CLI plus JSON support in SQL Server to manage a list of Azure SQL Managed Instances: Sometime you would need to know how many Managed Instance you have created in Azure cloud. Although you can find all information about the Azure SQL Managed Instances in Azure […]

Read More

Formatting Queries As JSON With FOR JSON

Kevin Feasel

2018-08-23

JSON

Eduardo Pivaral shows off the FOR JSON functionality in SQL Server 2016 and later: For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows: SELECT TOP 10 id, dataVarchar, dataNumeric, […]

Read More

Categories

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