如何通过SQL查询检查JSON函数? [英] How to check are there JSON Functions by SQL query?

问题描述

在SQL中有 JSON函数2016 ，例如JSON_VALUE，JSON_QUERY等.

There are JSON Function in SQL 2016 like JSON_VALUE, JSON_QUERY and other.

我想在查询中使用它，但是例如，我仍然拥有使用SQL 2014的旧服务器，不允许使用新功能.

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.

我可以通过查询检查是否存在JSON_VALUE之类的功能吗?像

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 ''

谢谢.

更新

如果我这样使用ckecking(感谢Rigerta Demiri)

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'

...我收到以下SQL异常(在2014 SQL Studio中):

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

'JSON_VALUE'不是公认的内置函数名称

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

可能是2014年的MSSQL解释程序尝试解析所有代码块，并且无法理解什么是JSON_VALUE?

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

推荐答案

由于它取决于您所安装的SQL Server的版本，并且由于您拥有不同的实例(甚至比SQL Server 2016更旧的实例)，因此您只需检查是否您要查询的数据库的兼容性级别等于130.

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.

您可以执行以下操作:

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

OPENJSON函数仅在兼容级别130下可用 (或更高).

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

您可以在文档中阅读.

发生的事情是因为"SQL Server显然不知道或不在乎要输入条件的哪个分支；无论如何它都会批处理所有语句."如这篇文章的答案所述:.

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 

您可以在其中获得有关动态SQL的更多信息的众多资源之一是

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

这篇关于如何通过SQL查询检查JSON函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！