If you need more control over formatting when it is offered by default, you can explicitly specify your own schema. The function will still return a table but with the columns defined by you. To specify the resultant table schema, use the WITH clause of the OPENJSON function. Here is the syntax for the OPENJSON function with an explicit schema:

OPENJSON( jsonExpression [ , path ] )   
[   
   WITH (    
       column_name data_type [ column_path ] [ AS JSON ]   
   [ , column_name data_type [ column_path ] [ AS JSON ] ]   
   [ , . . . n ]    
      )   
]   

When you use the WITH clause, you need to specify at least one column. For each column, you can specify the following attributes:

• column_name: This is the name of the output column.
• data_type: This is the data type for the output column.
• column_path: This is the value for the output column specified with the JSON path expression (it can be a JSON property or value of an array element); this argument is optional.
• AS JSON: Use this to specify that the property referenced in the column path represents an object or array; this argument is optional.

The best way to understand how the function works is to look at examples. The following code shows how to extract JSON properties as columns and their values as rows for JSON primitive data types:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Songs" :[{"Title":"Shine On You Crazy Diamond","Writers":"Gilmour, Waters, Wright"}, 
{"Title":"Have a Cigar","Writers":"Waters"}, 
{"Title":"Welcome to the Machine","Writers":"Waters"}, 
{"Title":"Wish You Were Here","Writers":"Gilmour, Waters"}], 
"Members":{"Guitar":"David Gilmour","Bass Guitar":"Roger Waters","Keyboard":"Richard Wright","Drums":"Nick Mason"} 
}'; 
SELECT * FROM OPENJSON(@json) 
WITH 
( 
  AlbumName NVARCHAR(50) '$.Album', 
  AlbumYear SMALLINT '$.Year', 
  IsVinyl      BIT '$.IsVinyl' 
); 

The result of the previous action is a table defined with the WITH statement:

You can add a fourth column to show band members. Here is the code:

SELECT * FROM OPENJSON(@json) 
WITH 
( 
  AlbumName NVARCHAR(50) '$.Album', 
  AlbumYear SMALLINT '$.Year', 
  IsVinyl  BIT '$.IsVinyl', 
  Members  VARCHAR(200) '$.Members' 
); 

Here is the resultant table:

The result might be unexpected, but the value for the Members property is an object, and therefore the function returns NULL since the JSON path is in default lax mode. If you specified the strict mode, the returned table would be empty and an error would be raised. To solve the problem and show the value of the Members property you need to use the AS JSON option to inform SQL Server that the expected data is properly JSON-formatted, as shown in the following code:

SELECT * FROM OPENJSON(@json) 
WITH 
( 
  AlbumName NVARCHAR(50) '$.Album', 
  AlbumYear SMALLINT '$.Year', 
  IsVinyl  BIT '$.IsVinyl', 
  Members  VARCHAR(MAX) '$.Members' AS JSON 
 
); 

Now it should return the expected result, but it returns an error:

Msg 13618, Level 16, State 1, Line 70
AS JSON option can be specified only for column of nvarchar(max) type in WITH clause.

As the error message clearly says, the AS JSON option requires a column with the nvarchar(max) data type. Finally, here is the code that works and returns the expected result:

SELECT * FROM OPENJSON(@json) 
WITH 
( 
  AlbumName NVARCHAR(50) '$.Album', 
  AlbumYear SMALLINT '$.Year', 
  IsVinyl  BIT '$.IsVinyl', 
  Members  NVARCHAR(MAX) '$.Members' AS JSON 
); 

To combine property values from different levels and convert them to a tabular format, you would need to have multiple calls of the OPENJSON function. The following example lists all songs and authors and shows the appropriate album name:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Songs" :[{"Title":"Shine On You Crazy Diamond","Writers":"Gilmour, Waters, Wright"}, 
{"Title":"Have a Cigar","Writers":"Waters"}, 
{"Title":"Welcome to the Machine","Writers":"Waters"}, 
{"Title":"Wish You Were Here","Writers":"Gilmour, Waters"}], 
"Members":{"Guitar":"David Gilmour","Bass Guitar":"Roger Waters","Keyboard":"Richard Wright","Drums":"Nick Mason"} 
}'; 
SELECT s.SongTitle, s.SongAuthors, a.AlbumName FROM OPENJSON(@json) 
WITH 
( 
  AlbumName NVARCHAR(50) '$.Album', 
  AlbumYear SMALLINT '$.Year', 
  IsVinyl BIT '$.IsVinyl', 
  Songs  NVARCHAR(MAX) '$.Songs' AS JSON, 
  Members NVARCHAR(MAX) '$.Members' AS JSON 
 
) a 
CROSS APPLY OPENJSON(Songs) 
WITH 
( 
  SongTitle NVARCHAR(200) '$.Title', 
  SongAuthors NVARCHAR(200) '$.Writers' 
)s; 

This time the result meets expectations. No hidden catch! Here is the result: