SELECT query on OpenJSON using SQL Server 2016

OpenJSON converts an array of objects in a variable in JSON Format to a rowset
that can be queried with standard SQL Select statement.

Here is an example:

We are going to look at a JSON list of my first batch of students who took my course shown here. 

["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]

This is the result of running OpenJSON using the above:

Now you can run a SELECT query with a with clause on the rows returned by OpenJSON as shown here:

The first member "wclass" has nulls for the selected columns. It exists because it actually was in the original XML that got converted to JSON.
Here are my more recent JSON related articles:

JSON validation in SQL Server:
https://hodentekmsss.blogspot.com/2016/11/using-json-validator-in-sql-server.html

Nested JSON using SQL Server 2012:
https://hodentekmsss.blogspot.com/search?q=json

Retrieve JSON formatted data from SQL Anywhere 17
https://hodentekmsss.blogspot.com/2016/11/retrieve-data-from-sql-anywhere-17-in.html

Change compatibility level of the database and JSON in SQL Server 2016

Working with SQL Server and  JSON  requires a compatibility level of 130 (SQL Server 2016). The post here describes how you can convert a JSON String and format a table in SQL Server.

The TestPubs (copy of pubs legacy database from Microsoft) database in an instance of SQL Server 2016 brought over by a data-tier related process does not change its compatibility level. However you can change its compatiblity level from 100 to 130 and with compatibility 130, the following query takes in a JSON string and converts it to a table with defaults. Here type 5 means it is an object.

declare @json nvarchar(150)
SET @json=N'[{"color": "red","value":"#f00"},{"color":"green","value":"#0f0"},{"color":"blue","value":"#00f"},{"color":"cyan","value":"#0ff"}]';
SELECT * From OPENJSON(@json)

Json130Open_00

Now if I revert the compatibility level to 100 and try the same query as before, I spawn an error as shown.

Json100Open_00