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

Searching Complex JSON With SQL Server

Kevin Feasel

2019-02-27

JSON

Bert Wagner gives us a way that you can quickly search through complicated JSON: Computed column indexes make querying JSON data fast and efficient, especially when the schema of the JSON data is the same throughout a table. It’s also possible to break out a well-known complex JSON structure into multiple SQL Server tables. However, what […]

Read More

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

Categories

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