When you don't specify a schema for returned results, the OPENJSON function returns a table with three columns:

• Key: This is the name of a JSON property or the index of a JSON element. The data type of the column is nvarchar, the length is 4,000, collation is Latin1_General_BIN2, and the column does not allow null values.
• Value: This is the value of the property or index defined by the key column. The data type of the column is nvarchar(max), it inherits collation from the input JSON text, and nulls are allowed.
• Type: The JSON data type of the value. The data type of the column is tinyint. Following table lists the possible values for this column and appropriate descriptions:

OPENJSON mapping of JSON data types

OPENJSON returns only one table; therefore only first-level properties are returned as rows. It returns one row for each JSON property or array element. To demonstrate the different results provided by the OPENJSON function, use the following JSON data with the information about the album Wish You Were Here by the British band Pink Floyd. You will provide JSON data as an input string and call the function without specifying an optional path argument:

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

The function has been invoked without the path expression; simply to convert the whole JSON document into a tabular format. Here is the output of this action:

As you can see, five rows were generated (one row for each JSON property), property names are shown in the key column, and their values in the value column.

The input JSON expression must be well formatted; otherwise, an error occurs. In the following code, a leading double quote for the Year property is intentionally omitted:

DECLARE @json NVARCHAR(500) = '{ 
"Album":"Wish You Were Here", 
Year":1975, 
"IsVinyl":true 
}'; 
SELECT * FROM OPENJSON(@json);

Of course, the optimizer does not forgive this small mistake and its reaction is very conservative:

Msg 13609, Level 16, State 4, Line 23
JSON text is not properly formatted. Unexpected character 'Y' is found at position 34.

As already mentioned and demonstrated, only first-level properties are returned with the OPENJSON function. To return properties within complex values of a JSON document (arrays and objects), you need to specify the path argument. In this example, assume you want to return the Songs fragment from the initial Wish You Were Here JSON string:

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

The $ path expression represents the context item and $.Songs refers to the Songs property and actually extracts this fragment from the JSON document. The rest of the document must be valid; otherwise, the path expression cannot be evaluated.

Here is the result:

You can see four entries for four elements in the JSON array representing songs from this album. Since they contain objects, their values are still in the JSON format in the column value.

When you do the same for the Members property, you get a nice list of properties with their names and values:

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

Here is the result:

Note that the returned type this time is 1 (string), while in the previous example it was 5 (object).

The function returns an error if the JSON text is not properly formatted. To demonstrate this, the initial string has been slightly changed: a leading double quote has been omitted for the element Drums (value Nick Mason). Therefore, the string is not JSON valid. Invoke the OPENJSON function for such a string:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish you Were Here", 
"Members":{"Guitar":"David Gilmour","Bass Guitar":"Roger Waters","Keyboard":"Richard Wright","Drums":Nick Mason", "Vocal":"Syd Barrett"} 
}'; 
SELECT * FROM OPENJSON (@json,'$.Members'); 

Here is the result:

Msg 13609, Level 16, State 4, Line 15
JSON text is not properly formatted. Unexpected character 'N' is found at position 417

You can see an error message, but also the returned table. The table contains three rows, since the first three properties of the complex Member property are JSON conforming. Instead of a fourth row, an error message has been generated and the fifth row is not shown either, although it is well formatted.

What would happen if the JSON path expression points to a scalar value or to a non-existing property? In the default JSON path mode (lax), the query would return an empty table; and when you specify strict mode, in addition to an empty table, an error message is shown (a batch-level exception is raised), as shown in the following examples:

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, N'$.Members.Guitar'); 
SELECT * FROM OPENJSON(@json, N'$.Movies'); 

Both queries return an empty table:

The same calls with the strict option end up with error messages:

SELECT * FROM OPENJSON(@json, N'strict $.Members.Guitar'); 

The result for the preceding query is the first error message:

Msg 13611, Level 16, State 1, Line 12
Value referenced by JSON path is not an array or object and cannot be opened with OPENJSON

The second query from the preceding example:

SELECT * FROM OPENJSON(@json, N'strict $.Movies'); 

The result for the preceding query is the second error message:

Msg 13608, Level 16, State 3, Line 13
Property cannot be found on the specified JSON path.

You can use OPENJSON, not only to convert JSON data into a tabular format, but also to implement some non-JSON related tasks.