I've got the following JSON.
[
{
"attributes": {
"2003": "Some text",
"2004": 0,
"2006": 0,
"2008": 0,
"2011": 0,
"2120": 0
},
"path": "/Path1",
"changeDate": "2019-11-11T13:56:37.987Z",
"guid": "00000000-0000-0000-0000-000000000000",
"version": 0,
"timestamp": "2019-11-11T14:21:14.86427Z"
},
{
"attributes": {
"2121": 0,
"2297": 0,
"2298": 0,
"2299": 0,
"2805": 0,
"8501": 0,
"12004": 0,
"13266": 0,
"13282": 0
},
"path": "P:/Path2/SubPath",
"changeDate": "2019-11-11T13:55:35.943Z",
"guid": "705b30ab-53b0-42ee-bb98-6d80daae2e18",
"version": 1,
"timestamp": "2019-11-11T09:08:54.417Z"
},
{
"attributes": {},
"path": "PP:/OneMorePath",
"changeDate": "2019-11-11T14:20:49.5Z",
"guid": "b9aac8f3-1f2a-4b52-b8d8-af6b654d3f0f",
"version": 41,
"timestamp": "2019-11-11T13:26:24.723Z"
}
]
For this I have two SQL Server tables.
CREATE TABLE [dbo].[Foo]
(
[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT(NEWID()),
[Path] NVARCHAR(128) NOT NULL,
[ChangeDate] DATETIME NOT NULL,
[Guid] UNIQUEIDENTIFIER NOT NULL,
[Version] INT NOT NULL,
[Timestamp] DATETIME NOT NULL
)
CREATE TABLE [dbo].[FooAttributes]
(
[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT(NEWID()),
[IdFoo] UNIQUEIDENTIFIER NOT NULL,
[Key] INT NOT NULL,
[Value] NVARCHAR(255) NOT NULL
)
For the header data I can query it in SQL Server.
SELECT *
FROM OPENJSON(@Json)
WITH (
[Path] NVARCHAR(128) 'strict $.path',
[ChangeDate] DATETIME2 '$.changeDate',
[Guid] UNIQUEIDENTIFIER '$.guid',
[Version] INT '$.version',
[Timestamp] DATETIME2 '$.timestamp'
)
And for this I get this result.
Path | ChangeDate | Guid | Version | Timestamp
------------------------------------------------------------------------------------------------------------------------------------------------
/Path1 | 2019-11-11 13:56:37.9870000 | 00000000-0000-0000-0000-000000000000 | 0 | 2019-11-11 14:21:14.8642700
P:/Path2/SubPath | 2019-11-11 13:55:35.9430000 | 705B30AB-53B0-42EE-BB98-6D80DAAE2E18 | 1 | 2019-11-11 09:08:54.4170000
PP:/OneMorePath | 2019-11-11 14:20:49.5000000 | B9AAC8F3-1F2A-4B52-B8D8-AF6B654D3F0F | 41 | 2019-11-11 13:26:24.7230000
This is ok. But I can't find any possibility to query the attributes to get this in the kind shown below because this is not an array of key/value pairs but fields with values.
IdFoo | AttributeName | AttributeValue
---------------------------------------
<IdFoo1> | 2003 | Some text
<IdFoo1> | 2004 | 0
<IdFoo1> | 2006 | 0
<IdFoo1> | 2008 | 0
<IdFoo1> | 2011 | 0
<IdFoo1> | 2120 | 0
<IdFoo2> | 2121 | 0
<IdFoo2> | 2297 | 0
<IdFoo2> | 2298 | 0
<IdFoo2> | 2299 | 0
<IdFoo2> | 2805 | 0
<IdFoo2> | 8501 | 0
<IdFoo2> | 12004 | 0
<IdFoo2> | 13266 | 0
<IdFoo2> | 13282 | 0
Is there something possible in SQL Server 2016 to get this substructure as wanted?
You need to parse the input JSON array with explicit schema using AS JSON to specify that the $attributes property contains an inner JSON object. After that you need to use additional CROSS APPLY operator with another OPENJSON() call with default schema (without the WITH clause), that returns a table with key, value and type columns.
JSON:
DECLARE @json nvarchar(max) = N'[
{
"attributes": {
"2003": "Some text",
"2004": 0,
"2006": 0,
"2008": 0,
"2011": 0,
"2120": 0
},
"path": "/Path1",
"changeDate": "2019-11-11T13:56:37.987Z",
"guid": "00000000-0000-0000-0000-000000000000",
"version": 0,
"timestamp": "2019-11-11T14:21:14.86427Z"
},
{
"attributes": {
"2121": 0,
"2297": 0,
"2298": 0,
"2299": 0,
"2805": 0,
"8501": 0,
"12004": 0,
"13266": 0,
"13282": 0
},
"path": "P:/Path2/SubPath",
"changeDate": "2019-11-11T13:55:35.943Z",
"guid": "705b30ab-53b0-42ee-bb98-6d80daae2e18",
"version": 1,
"timestamp": "2019-11-11T09:08:54.417Z"
},
{
"attributes": {},
"path": "PP:/OneMorePath",
"changeDate": "2019-11-11T14:20:49.5Z",
"guid": "b9aac8f3-1f2a-4b52-b8d8-af6b654d3f0f",
"version": 41,
"timestamp": "2019-11-11T13:26:24.723Z"
}
]'
Statement:
SELECT
j1.Guid,
j2.[key] AS AttrName,
j2.[value] AS AttrValue
FROM OPENJSON(@json, '$') WITH (
Guid UNIQUEIDENTIFIER '$.guid',
-- other columns definitions
attributes nvarchar(max) '$.attributes' AS JSON
) j1
CROSS APPLY OPENJSON(j1.attributes) j2
Results:
Guid AttrName AttrValue
00000000-0000-0000-0000-000000000000 2003 Some text
00000000-0000-0000-0000-000000000000 2004 0
00000000-0000-0000-0000-000000000000 2006 0
00000000-0000-0000-0000-000000000000 2008 0
00000000-0000-0000-0000-000000000000 2011 0
00000000-0000-0000-0000-000000000000 2120 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 2121 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 2297 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 2298 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 2299 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 2805 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 8501 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 12004 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 13266 0
705b30ab-53b0-42ee-bb98-6d80daae2e18 13282 0