Return All JSON Values and Attributes with Attributes as Columns in SQL Server 2017

Question

Using a simple example of a table with just two columns—an id and a long JSON string—how can I easily return all JSON values and attributes with the attributes as columns?

I know I can readily use JSON_VALUE and specify a specific key/attribute, but I don’t want to have a long list of numerous JSON_VALUE for every possible key. Is there a dynamic way of doing this?

Say, for example, I have 100 JSON keys and the result should be an id column with another 100 key columns and their values?

Thank you.

Answer 1

If your JSON object is just a one-level list of key-value pairs, you can call OPENJSON:

DECLARE @mockup TABLE(Id INT, SomeJSON NVARCHAR(MAX));

INSERT INTO @mockup VALUES
 (1,N'{"key1":"value1","key2":"value2"}')
,(2,N'{"key1":"value1","key5":"value5"}')
,(3,N'{"keyA":"valueA","keyB":"valueB","keyZ":"valueZ"}');

SELECT t.Id
      ,A.*
FROM @mockup t
CROSS APPLY OPENJSON(t.SomeJSON) A;

The result

1   key1    value1  1
1   key2    value2  1
2   key1    value1  1
2   key5    value5  1
3   keyA    valueA  1
3   keyB    valueB  1
3   keyZ    valueZ  1

To get this in tabular format side-by-side you can use PIVOT, conditional aggregation or - probably best - a WITH clause:

SELECT t.Id
      ,A.*
FROM @mockup t
CROSS APPLY OPENJSON(t.SomeJSON) 
WITH(key1 NVARCHAR(10)
    ,key2 NVARCHAR(10)
    ,key5 NVARCHAR(10)
    ,keyA NVARCHAR(10)
    ,keyB NVARCHAR(10)
    ,keyZ NVARCHAR(10)
    ) A;

The result

Id  key1    key2    key5    keyA    keyB    keyZ
1   value1  value2  NULL    NULL    NULL    NULL
2   value1  NULL    value5  NULL    NULL    NULL
3   NULL    NULL    NULL    valueA  valueB  valueZ

In any case you must know all possible keys in advance. This approach allows for best naming and you can specify the appropriate type.

In case you don't know all keys in advance, you might look into dynamic SQL. You can use the statement above to read all keys and create the fitting statement/WITH-clause dynamically.