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

  • Execute Insert command and return inserted Id in Sql
  • I want to display records of current date in order of pending, solved, cancel
  • SQL Server : sort a 3 columns table into a 7 colums table
  • Roundup in an update sql query
  • Most efficient T-SQL way to pad a varchar on the left to a certain length?
  • Update one of 2 duplicates in an sql server database table
  • 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'
        SELECT ''



    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)
        SELECT JSON_VALUE([Value], '$.MyPath')
        FROM [dbo].[MyTable] 
        WHERE [Name] = 'MyProperty'
        SELECT 'not allowed'

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

    ‘JSON_VALUE’ is not a recognized built-in function name

    enter image description here

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

    One Solution collect form web for “How to check are there JSON Functions by SQL query?”

    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)
    declare @jsoninfo nvarchar(max)
    set @jsoninfo=N'{  
           "tags":["sport", "water polo"]  
    select json_value(@jsoninfo,'$')  as town

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

    as you can read in the documentation.


    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)
        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,''$'')  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
        select 'Version prior to 130!' as [message]

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.