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?
(n)varchar
; unless you have some kind of look up table that tells you the data types of the elements?