Last active
January 13, 2017 19:07
-
-
Save billinkc/a93401a8516e9cb5db5ffba9b005643d to your computer and use it in GitHub Desktop.
Testing JSON with SQL Server 2016 and I'm confused on the seemingly extraneous square bracket around the child table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET NOCOUNT ON; | |
IF OBJECT_ID('tempdb..#Sales') IS NULL | |
BEGIN | |
CREATE TABLE #Sales | |
( | |
RecordKey int NOT NULL | |
, SalesDate date NOT NULL | |
, CONSTRAINT PK_tmp_Sales PRIMARY KEY (RecordKey) | |
) | |
INSERT INTO | |
#Sales | |
SELECT | |
1 AS RecordKey | |
, CAST('2017-01-13' AS date) AS SalesDate; | |
END | |
IF OBJECT_ID('tempdb..#SalesDetail') IS NULL | |
BEGIN | |
CREATE TABLE #SalesDetail | |
( | |
RecordKey int NOT NULL | |
, SalesRecordKey int NOT NULL | |
, Quantity int NOT NULL | |
, CONSTRAINT PK_tmp_SalesDetail PRIMARY KEY (RecordKey) | |
) | |
INSERT INTO | |
#SalesDetail | |
SELECT | |
1 AS RecordKey | |
, 1 AS SalesRecordKey | |
, 100 AS Quantity; | |
END | |
SELECT | |
* | |
FROM | |
#Sales AS S | |
INNER JOIN | |
#SalesDetail AS SD | |
ON SD.SalesRecordKey = S.RecordKey | |
FOR JSON AUTO; | |
DECLARE | |
@json nvarchar(max); | |
/* | |
SELECT @json = N'[ | |
{ | |
"RecordKey":1, | |
"SalesDate":"2017-01-13", | |
"SD":[ | |
{ | |
"RecordKey":1, | |
"SalesRecordKey":1, | |
"Quantity":100 | |
} | |
] | |
} | |
]'; | |
*/ | |
SELECT | |
@json = | |
( | |
SELECT | |
* | |
FROM | |
#Sales AS S | |
INNER JOIN | |
#SalesDetail AS SD | |
ON SD.SalesRecordKey = S.RecordKey | |
FOR JSON AUTO | |
); | |
SELECT | |
* | |
FROM | |
OPENJSON(@json) | |
WITH | |
( | |
RecordKey int '$.RecordKey' | |
, SalesDate date '$.SalesDate' | |
-- Why does this not find it? | |
-- Because the AUTO defines SD with square brackets | |
-- "SD":[{"RecordKey":1,"SalesRecordKey":1,"Quantity":100}] | |
, Quantity int '$.SD.Quantity' | |
)AS OJ; | |
SELECT @json = N'[ | |
{ | |
"RecordKey":1, | |
"SalesDate":"2017-01-13", | |
"SD": | |
{ | |
"RecordKey":1, | |
"SalesRecordKey":1, | |
"Quantity":100 | |
} | |
} | |
]'; | |
-- Convert JSON Data to Rows and Columns with OPENJSON | |
-- https://msdn.microsoft.com/en-us/library/dn921879.aspx | |
SELECT | |
* | |
FROM | |
OPENJSON(@json) | |
WITH | |
( | |
RecordKey int '$.RecordKey' | |
, SalesDate date '$.SalesDate' | |
-- Now it works | |
, Quantity int '$.SD.Quantity' | |
)AS OJ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment