OPENJSON does not work in SQL Server?

sql-server json sql-server-2016
Iva picture Iva · Apr 7, 2016 · Viewed 31.7k times · Source

I want to use JSON functions in SQL Server 2016, but when I try to execute OPENJSON function, I get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'openjson'.

Why it does not work? I have SQL Server 2016 RC version.

Answer

Jovan MSFT picture Jovan MSFT · Apr 7, 2016

Could you check compatibility level on database? OPENJSON is available under compatibility level 130. Could you try to execute:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130

Also, if you are using JSON on Azure SQL Database, note that even new databases are created under 120 compatibility level so you should change it if you want to use OPENJSON. Also, if you are using it in Azure SQL Database, run select @@version to see is this V12 server. You should see something like:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 25 2016 15:11:30 Copyright (c) Microsoft Corporation

If you see some lower version (e.g. 11.xxx) you probably have database on old architecture where JSON is not supported.

Regards,

Jovan

Related questions

Accessing JSON Array in SQL Server 2016 using JSON_VALUE

I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code - IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData') DROP TABLE JsonData; go CREATE TABLE JsonData(JsonData nvarchar(max)); …

Read More
Create nested JSON arrays using FOR JSON PATH

I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship. I would like the values of the secondary table to be nested as array properties of the …

Read More
How to check are there JSON Functions by SQL query?

There are JSON Function in SQL 2016 like JSON_VALUE, JSON_QUERY and other. I would like to use it in my queries, but I still have old servers with SQL 2014, for example, that are not allowed to use the new …

Read More