How to check are there JSON Functions by SQL query?

Question

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

Can I check are there functions like JSON_VALUE by query? Something like

IF operator_exists('JSON_VALUE')
    SELECT JSON_VALUE([Value], '$.MyPath')
      FROM [dbo].[MyTable] 
      WHERE [Name] = 'MyProperty'
ELSE
    SELECT ''

Thanks.

UPDATE

If I use ckecking like this (thanks Rigerta Demiri)

DECLARE @compatibility_level int
SELECT @compatibility_level= compatibility_level FROM sys.databases WHERE name = 'MyDbName'
IF (@compatibility_level >= 130)
BEGIN
    SELECT JSON_VALUE([Value], '$.MyPath')
    FROM [dbo].[MyTable] 
    WHERE [Name] = 'MyProperty'
END
    SELECT 'not allowed'

... I get the following SQL exception (on 2014 SQL Studio):

'JSON_VALUE' is not a recognized built-in function name

May be 2014 MSSQL interpretator try to parse all blocks of code and cannot understand what is JSON_VALUE?

Solution

Since it depends on the version of SQL Server that you have installed and since you have different instances (even older ones than SQL Server 2016) you can just check if the compatibility level of the database where you are trying to query is equal to 130.

You could do the following:

declare @compatibility_level int
select @compatibility_level= compatibility_level from sys.databases where name = 'TestDB'

if (@compatibility_level >= 130)
begin
declare @jsoninfo nvarchar(max)

set @jsoninfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"bristol",  
         "county":"avon",  
         "country":"england"  
       },  
       "tags":["sport", "water polo"]  
    },  
    "type":"basic"  
 }'  

select json_value(@jsoninfo,'$.info.address.town')  as town
end

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

as you can read in the documentation.

EDIT:

What you got happens because apparently "SQL Server doesn't know or care which branch of a conditional will be entered; it validates all of the statements in a batch anyway." as stated in the answer of this post: T-Sql appears to be evaluating “If” statement even when the condition is not true.

So, the workaround would be to create the whole statement as a dynamic string. Like this:

declare @compatibility_level int
select @compatibility_level= compatibility_level from sys.databases where name = 'TradingDWH'
if (@compatibility_level >= 130)
    begin

    declare @sql nvarchar(max);
    set @sql = ' declare @jsoninfo nvarchar(max) ' + ' set @jsoninfo=N''{ "info":{' + ' "type":1, "address":{ "town":"bristol", "county":"avon", "country":"england" }, "tags":["sport", "water polo"] }, "type":"basic" }'
    set @sql = @sql + 'select json_value(@jsoninfo,''$.info.address.town'')  as town'
    select @sql
    --exec sp_executesql @sql

    -- or your own query, like this:
    declare @sql2 nvarchar(max);
    declare @MyProperty nvarchar(100) = 'YourProperty'

    set @sql2 = ' SELECT JSON_VALUE([Value], ''$.MyPath'') '
    set @sql2 = @sql2 + 'FROM [dbo].[MyTable] WHERE [Name] = @MyProperty '
    select @sql2 
    --exec sp_executesql @sql2, N'@MyProperty nvarchar(100)', @MyProperty

    end
else 
begin 
    select 'Version prior to 130!' as [message]
end 

One of many resources where you can read more about dynamic SQL is Don’t Fear Dynamic SQL.

Comments

[hcp]

Thank you for your reply. But when I use JSON_VALUE inside begin/end I get "'JSON_VALUE' is not a recognized built-in function name"

[Rigerta]

If you check the updated answer, I also added a call to that function, tested it, it works. Please replace the database name (TestDB) with your database name.

[hcp]

Hm.. It is very strange.

[Rigerta]

Did you try the sample code I posted? Maybe what you return is too long of a string and json_value throws an error..could you post some sample data as well?

[hcp]

Yes I tried. I updated my question and attached the proof image.

[Rigerta]

@hcp, for me it had worked since I tried on SQL Server 2016. You are right and I edited my answer accordingly.

[hcp]

"would be to create the whole statement as a dynamic string." Do you know how to do it? Thanks.

[Clay]

SQL parses the whole procedure and fails...so you'll have to make the possibly-supported procedure in a separate procedure from the check.

[Rigerta]

@hcp, updated. Also with a link on an article on dynamic SQL. Take a look! :)

[hcp]

@RigertaDemiri Thank you very much! It works great, but only one typo in your code: instead of <basic" }'> I use <basic" }'''> (3 single quotes on the end instead of 1).