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
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
