Get names of keys from JSON in SQL Server

Question

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?

Answer 1

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

Answer 2

Using As Json with Cross apply and another OpenJson can give you the desired results:

SELECT [Guid],  [key], [value]
FROM OPENJSON(@Json)
      WITH (
        [Guid] UNIQUEIDENTIFIER '$.guid',
        [Attributes] NVARCHAR(max) '$.attributes' As Json
      )
CROSS APPLY OPENJSON(Attributes)    

Note that the key is nvarchar(4000) and value is nvarchar(max).

See a live demo on DbFiddle<>UK