A common mistake is treating JSON as automatically relational. When an array path omits its index, JSON_VALUE returns NULL; OPENJSON instead shreds the selected document portion into rows and typed columns without document preparation. section 3.5
Employee address extraction
OPENJSON(@EmployeeAddress, '$.EmployeeAddress[0]') selects one array element. A WITH clause maps EmployeeID, address paths, and SQL data types into relational columns. section 3.5
Listing 3.24 Shredding the JSON data into relational values
DECLARE @EmployeeAddress NVARCHAR(MAX) ;
SET @EmployeeAddress = (
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')
) ;
SELECT *
FROM OPENJSON(@EmployeeAddress, '$.EmployeeAddress[0]')
WITH (
EmployeeID SMALLINT,
Line1 NVARCHAR(64) '$.Address.Line1',
City NVARCHAR(64) '$.Address.City',
[State] NCHAR(2) '$.Address.State',
ZipCode NVARCHAR(10) '$.Address.ZipCode'
) ;
Inspect the complete T-SQL pattern for shredding an employee address JSON document into relational values. section 3.5
