Skip to content

Instantly share code, notes, and snippets.

@billinkc
Last active January 13, 2017 19:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save billinkc/a93401a8516e9cb5db5ffba9b005643d to your computer and use it in GitHub Desktop.
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
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