Discovery's Weird JSON, SQL Server, and OPENJSON-CodePudding

I am using an API from Discovery to extract some information from our virtual machines but the JSON for some reasons have the headings in one node and the values in another and they are in square brackets and I am having a hard getting to the end result.

This is a watered-down version of the JSON

DECLARE @JSON nvarchar(max) = N'[
    {
        "headings": [
            "vm_type",
            "Hostname"
            ],
        "results": [
            [
                "AWS EC2 Instance",
                null
            ],
            [
                "AWS EC2 Instance",
                null
            ]
        ]
    }
]'

SET @JSON = SUBSTRING(@JSON,2,LEN(@JSON) - 2)
SELECT *
FROM OPENJSON(@JSON)

SELECT *
FROM OPENJSON(@JSON,'$.results')

Is there a way to turn this into a table with the headings as column names and the results in the same order as its value?

CodePudding user response：

You have an array of arrays, so this is effectively a dynamic pivot.

To do a dynamic pivot you need dynamic SQL. The easiest way to pivot is not usually to use PIVOT but to use conditional aggregation with MAX(CASE

DECLARE @sql nvarchar(max) = N'
SELECT
  '   (
    SELECT STRING_AGG(
        QUOTENAME(j.value)   N' = MAX(CASE WHEN j2.[key] = '   j.[key]   ' THEN j2.value END)',
        ',
  ') WITHIN GROUP (ORDER BY j.[key])
    FROM OPENJSON(@JSON, '$[0].headings') j
)   '
FROM OPENJSON(@JSON, ''$[0].results'') j1
CROSS APPLY OPENJSON(j1.value) j2
GROUP BY
  j1.[key];
';

PRINT @sql; --for testing

EXEC sp_executesql
  @sql,
  N'@JSON nvarchar(max)',
  @JSON = @JSON;

db<>fiddle

Note correct use of QUOTENAME to quote column names, and use of sp_executesql with a parameter to pass in actual data, rather than injecting it directly into the query text.