T-SQL OPENJSON WITH clause

Question

This question is based on information from the JSON data in SQL Server article from the online documentaion:

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json)
  WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
  );

Each line contained in the WITH clause appears to be composed of:

• The Alias such as firstName
• The DataType such as NVARCHAR(50)
• The JSON column name such as '$.info.name'

Is there a way to parse the JSON string into columns without having to explicitly define each column?

In other words, the JSON string contains the column names, for my purposes the Alias could have the same name as the JSON string column name.

Answer 1

Is there a way to parse the JSON string into columns without having to explicitly define each column?

In short, no. The SQL language has very strict rules that require you to know about the columns in the output at query compile time before looking at any data.

If you want it to build columns dynamically, you need dynamic SQL, which means running a query to find out about your columns, using the results to build a new SQL command on the fly using that result, and then running the new SQL.

Answer 2

As @JoelCoehoorn has already explained, the short answer is no. But, in this situation, you may try an approach based on dynamically created schema, again using OPENJSON() as a parser. What you need to do is to parse the input JSON recursivly and get all distinct paths (@Charlieface's feedle is a great starting point). Of course, this solution assumes, that the input JSON is JSON array of JSON objects, without nested JSON arrays.

Statement:

-- JSON
DECLARE @json NVARCHAR(MAX);
SET @json = 
N'[
   {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
   {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"},
   {"id": 3, "info": {"surname": "Kane"}}
]';

-- Schema
DECLARE @schm nvarchar(max)
;WITH pathCTE AS (
   SELECT
     CONVERT(nvarchar(max), N'$') COLLATE DATABASE_DEFAULT AS path,
     CONVERT(nvarchar(max), j.value) COLLATE DATABASE_DEFAULT AS [value],
     j.type
   FROM OPENJSON(@json) j
   UNION ALL
   SELECT
      CONVERT(nvarchar(max), p.path + CASE WHEN j.type <> 4 THEN CONCAT(N'."', j.[key], N'"') END) COLLATE DATABASE_DEFAULT,
      CONVERT(nvarchar(max), j.value) COLLATE DATABASE_DEFAULT,
      j.type
   FROM pathCTE p
   CROSS APPLY OPENJSON(p.value) j
   WHERE p.type IN (4, 5)
), schemaCTE AS (
   SELECT DISTINCT path
   FROM pathCTE
   WHERE [type] IN (0, 1, 2, 3)
)
SELECT @schm = STRING_AGG(CONCAT(N'[', path, N'] nvarchar(max) ''', path, N''''), ',')
FROM schemaCTE

-- Statement
DECLARE @stmt nvarchar(max)
DECLARE @prms nvarchar(max)
SET @stmt = CONCAT(
   N'SELECT * FROM OPENJSON(@json) WITH (',
   @schm,
   N')'
)
SET @prms = N'@json nvarchar(max)'

-- Execution
DECLARE @rc int
EXEC @rc = sp_executesql @stmt, @prms, @json
IF @rc <> 0 PRINT 'Error'

Result:

$."age"	$."dob"	$."id"	$."info"."name"	$."info"."surname"
25	null	2	John	Smith
null	2005-11-04T12:00:00	5	Jane	Smith
null	null	3	null	Kane