Json to table without explicit key names

Question

I have a table with an VARCHAR(MAX) column which stores JSON key value pairs. The JSON document schema is simply a different number of key value pairs, no nesting, nor arrays in there.

I wish to build a query, which gives back the JSON in a tabular format, which is easy with the named elements (see WITH clause below):

DECLARE @MYJSONTABLE TABLE (ID INT IDENTITY NOT NULL PRIMARY KEY, MYDATA NVARCHAR(max) null)
INSERT INTO @MYJSONTABLE
(
    MYDATA
)
VALUES
(N'{"id": 2, "info": "some info", "age": 25}'),
(N'{"id": 5, "info": "other info", "dob": "2005-11-04T12:00:00"}')

SELECT p.ID,MYDATA.*
FROM @MYJSONTABLE p
CROSS APPLY 
OPENJSON(p.MYDATA)
WITH (
    id INT 'strict $.id',
    info NVARCHAR(50) '$.info',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
) AS MYDATA

While the output is exactly what I want, my issue with the above solution, that I don't know the key names in the JSON document neither, and how many are there, but still wish to return them all in the same tabular format.

If I omit the WITH clause above, the query do return all key value pairs, but the output goes "vertical" and each key in the JSON generates a new row.

Could the above query be modified to be dynamic, and return all key value pairs without explicitly specifying the JSON key names?

Answer 1

Perhaps something like this will work for you.

This uses a CTE to get the DISTINCT key's from your JSON. Then string aggregation to create a dynamic statement, which you can see from the PRINT statement.

Note that for your sample data, the column dob is not returned because it is outside of the initial JSON defined. If the first right brace (}) is removed, the column appears.

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(50) = N',' + @CRLF + N'                      ';

WITH Keys AS(
    SELECT DISTINCT J.[key]
    FROM dbo.YourTable YT
         CROSS APPLY OPENJSON(YT.JsonColumn) J)
SELECT @SQL = N'SELECT YT.ID,' + @CRLF +
              N'       J.*' + @CRLF +
              N'FROM dbo.YourTable YT' + @CRLF +
              N'     CROSS APPLY OPENJSON(YT.JsonColumn)' + @CRLF +
              N'                 WITH(' +
              STRING_AGG(QUOTENAME(K.[key]) + N' nvarchar(100)', @Delimiter) + N') J;'
FROM Keys K;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;

Note, this will not work with a table variable, unless you create a table type and then pass the TYPE as a parameter to sys.sp_executesql. This is why the above assumes a real table.