If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.

OPENJSON() is only available under compatibility level 130 or higher.

To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.

Example of Error

Here’s an example of some basic code that will cause this error.

USE Pets;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

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

When your database compatibility level is lower than 130, SQL Server can’t find and run the OPENJSON() function.

In my case, the database I was trying to run this on had a compatibility level of 120.

Check the Compatibility Level of the Database

You can query sys.databases to check the compatibility level of the database (or all databases if you prefer).

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 120                   |
+-----------------------+

As suspected, this database has a compatibility level of less than 130.

Solution 1

The most obvious solution is to increase the compatibility level of the database for which you’re trying to run OPENJSON() against.

ALTER DATABASE Pets  
SET COMPATIBILITY_LEVEL = 150;

Running that code will increase the database’s compatibility level to 150, which is more than high enough to support the OPENJSON() function.

If we check the compatibility level again, we can see that it’s increased to 150.

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 150                   |
+-----------------------+

Now we can run the original code without error.

USE Pets;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

Solution 2

If for some reason you can’t, or don’t want to, change the compatibility level of the database, you could switch to a database that already has the appropriate compatibility level.

Obviously, this may or may not be suitable, depending on whether you need to insert your parsed JSON into the database or not.

Anyway, to do this, you could query sys.databases for a suitable database.

SELECT 
    name,
    compatibility_level
FROM sys.databases;

Result:

+--------------------+-----------------------+
| name               | compatibility_level   |
|--------------------+-----------------------|
| master             | 150                   |
| tempdb             | 150                   |
| model              | 150                   |
| msdb               | 150                   |
| Music              | 150                   |
| Test               | 150                   |
| WideWorldImporters | 130                   |
| World              | 140                   |
| Pets               | 120                   |
+--------------------+-----------------------+

Fortunately in this case, all other databases are 130 or higher. So we could switch to any one of them.

USE World;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+