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.

If you do it without `WITH` at all then you get three columns: `key` `value` and `type`, each row is one property of the object (or one object in an array). This may still be useful for you in order to dynamically retrieve specific keys. If you give more detail on your use case there may be something to be done. Do you need the actual columns coming out of your function to be dynamic, or is it just that you want to get the data from the JSON dynamically? — Charlieface, Feb 13 '22 at 22:48
What I'm looking for in T-SQL is an equivalent to Oracle's "JSON_DATAGUIDE" to get the schema. specifically the "o:path" : "$.PO_ID", The JSON I'm working with is very simple, no nesting, no crazy formats just rows and columns. The twist is each client will have different columns in their output. One may have name, surname. Another may have street and restaurant. So what I'm needing is to be able to pull out the JSON record keys (aka column names). Christ, I'm lost with this. — TPV, Feb 13 '22 at 23:07
`OPENJSON` will only break open one level. You can get a full graph by using a recursive CTE to recursively open each array or object, for example https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df14592b1b87e6baba9ccde1ede0891f. Is this what you wanted? It's very unclear what your *actual* wanted results are. Please show what you are actually trying to achieve — Charlieface, Feb 13 '22 at 23:19

score 3 · Answer 1 · answered Feb 13 '22 at 22:19

3

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.

answered Feb 13 '22 at 22:19

Joel Coehoorn

399,467
113
570
794

Thanks, long story but was trying to avoid the dynamic SQL route as the JSON strings I'm consuming vary from client to client. Am attempting to create a function rather than a proc. Thanks again for taking the time. – TPV Feb 13 '22 at 22:24

Zhorov · Answer 2 · 2022-02-22T07:13:24.077

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

T-SQL OPENJSON WITH clause

2 Answers2