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?