OPENJSON does not get recognized in Azure SQL DB

Question

I want to use OPENJSON function in Azure SQL DB, but when I try to execute the function, I get the following error:

Msg 195, Level 15, State 10, Line 25 'OPENJSON' is not a recognized built-in function name.

I had researched and found this link saying

The OPENJSON function is available only under compatibility level 130 or higher. If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. Other JSON functions are available at all compatibility levels.

As suggested, checked compatibility level with following

select * from sys.databases

and confirmed it was 140 which is higher than the minimum requirement.

So, wouldn't it it work?

Answer 1

OPENJSON is a table-valued function, and it must appear in the a valid location for a table expression.

EG

select openjson('{}')

fails with

Msg 195, Level 15, State 10, Line 29 'openjson' is not a recognized built-in function name.

But

select * from openjson('{}')

works.

Answer 2

Ideally, it should work if the compatibility level is 130 or above.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/24/lesson-learned-34-does-azure-sql-database-support-openjson/

Answer 3

Three steps may help you:

1.Check your SQL Server version(Starting with 2016).

2.Check your Azure SQL database ompatibility level is 130 or higher.

3.Check your Azure SQL Database version. Run select @@version to see if it is the V12 server.

If you see some lower version (e.g. 11.xxx) you probably have database on old architecture where JSON is not supported. You need to update your Azure SQL Database to the latest V12 version.

Reference:OPENJSON does not work in SQL Server?

Hope this helps.