Mistake: treating a JSON path like an ordinary property name. A path begins with $. for the whole document, then follows object properties and array indexes. Omitting an array index returns NULL, even when the array contains one record. section 3.5
Use JSON paths to move between JSON and relational data:
- Generate hierarchy with FOR JSON PATH aliases such as Address.Line1. section 3.5
- Extract a scalar with JSON_VALUE(document, '$.EmployeeAddress[0].EmployeeID'). section 3.5
- Shred a hierarchy with OPENJSON and map nested nodes through paths in WITH. section 3.5
Updating an employee address
A generated EmployeeAddress array can identify the target row with JSON_VALUE(@EmployeeAddress, '$.EmployeeAddress[0].EmployeeID'), then OPENJSON can return EmployeeID, Line1, City, State, and ZipCode as 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 a complete OPENJSON query that selects an array element and maps nested address properties to typed columns. section 3.5
