Tutorial by Examples

Formats results of SELECT query as JSON text. FOR JSON PATH clause is added after query: SELECT top 3 object_id, name, type, principal_id FROM sys.objects FOR JSON PATH Column names will be used as keys in JSON, and cell values will be generated as JSON values. Result of the query would be an a...

FOR JSON PATH enables you to control format of the output JSON using column aliases: SELECT top 3 object_id as id, name as [data.name], type as [data.type] FROM sys.objects FOR JSON PATH Column alias will be used as a key name. Dot-separated column aliases (data.name and data.type) will be gen...

WITHOUT_ARRAY_WRAPPER option enables you to generate a single object instead of the array. Use this option if you know that you will return single row/object: SELECT top 3 object_id, name, type, principal_id FROM sys.objects WHERE object_id = 3 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER Single obje...

FOR JSON clause ignores NULL values in cells. If you want to generate "key": null pairs for cells that contain NULL values, add INCLUDE_NULL_VALUES option in the query: SELECT top 3 object_id, name, type, principal_id FROM sys.objects FOR JSON PATH, INCLUDE_NULL_VALUES NULL values in...

Wraps returned JSON array in additional root object with specified key: SELECT top 3 object_id, name, type FROM sys.objects FOR JSON PATH, ROOT('data') Result of the query would be array of JSON objects inside the wrapper object: { "data":[ {"object_id":3,&qu...

Automatically nests values from the second table as a nested sub-array of JSON objects: SELECT top 5 o.object_id, o.name, c.column_id, c.name FROM sys.objects o JOIN sys.columns c ON o.object_id = c.object_id FOR JSON AUTO Result of the query would be array of JSON objects: [ { &...

If you need some complex JSON structure that cannot be created using FOR JSON PATH or FOR JSON AUTO, you can customize your JSON output by putting FOR JSON sub-queries as column expressions: SELECT top 5 o.object_id, o.name, (SELECT column_id, c.name FROM sys.columns c WHERE o...