44773

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.

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.

<strong>UPDATE</strong>

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

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/6r2cu.jpg" alt="enter image description here">

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

Answer1:

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.

Recommend

  • Prolog showing results on the interpretator [duplicate]
  • How to select parent ids
  • How to setup a reverse proxy on several ports (tcp & udp)
  • Passing Query Parameters from HTML/JS App to Azure Server Script
  • What is the best data type to store boolean values in a database
  • File random access in J2ME
  • How to generate an asynchronous reset verilog always blocks with chisel
  • How do you keep a running instance for Google App Engine
  • Python function to read variable length blocks of data from file while open
  • Upgrade project from WorkLight 6.1 to MobileFirst 7.1
  • TFS - how do I sum child task hours to parent
  • Deploying a CodeRush plugin from the Community Site
  • How to pass solution folder as parameter in command line arguments (for debug)?
  • How to synchronize jQuery dialog box to act like alert() of Javascript
  • OOP Javascript - Is “get property” method necessary?
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • Using jQuery closest() method with class selector
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Illegal mix of collations for operation for date/time comparison
  • Master page gives error
  • Array.prototype.includes - not transformed with babel
  • javascript inside java/jsp code
  • Android Studio and gradle
  • Acquiring multiple attributes from .xml file in c#
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • How do you join a server to an Active Directory (domain)?
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How can I remove ASP.NET Designer.cs files?
  • Can't mass-assign protected attributes when import data from csv file
  • sending mail using smtp is too slow
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How can i traverse a binary tree from right to left in java?
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal