liveBook · Manning

For json clause

As explained in 100 SQL Server Mistakes and How to Avoid Them

FOR JSON PATH is a T-SQL JSON serialization mode that provides explicit control over the hierarchy and names of generated properties.

Mistake: treating FOR JSON modes as interchangeable. AUTO derives hierarchy from column order and JOIN order, while PATH lets aliases explicitly name nested properties. section 3.5

Generate an employee address document

SELECT 1 AS 'EmployeeID', '5331 Rexford Court' AS 'Address.Line1', 'Montgomery' AS 'Address.City', 'AL' AS 'Address.State', '36116' AS 'Address.ZipCode' FOR JSON PATH, ROOT ('EmployeeAddress'); section 3.5

Use the clause safely by remembering:

  • Use PATH when property names and hierarchy require granular control. section 3.5
  • Use AUTO cautiously: it requires a table, so an arbitrary FROM source and TOP 1 may be needed. section 3.5

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains AUTO and PATH modes, nested aliases, ROOT output, and practical AUTO limitations.
Examples
Uses an employee address document with nested address fields, ROOT output, storage, updating, and shredding.
What it assumes
Assumes basic SELECT, aliases, tables, and JSON structure while demonstrating JSON paths from zero.
What you can do after
Builds controlled nested JSON with PATH and processes resulting values through SQL Server JSON functions.
How it connects
Connects JSON serialization with JSON_VALUE, OPENJSON, SELECT, UPDATE, and JSON path expressions.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.