ISJSON in Azure Synapse SQL pool-CodePudding

I am having an issue with the ISJSON function in Azure Synapse SQL pool.

The code snippets below executes correctly in SQL Server 2016

--test1
SELECT myJsonField
FROM myTable
WHERE ISJSON(myJsonField) = 1

--test2
SELECT ISJSON('{"a":1}')

but when I execute the same code on an Azure Synapse SQL Pool I get the following error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

According to the documentation, this function should be available in Synapse:

CodePudding user response：

I have tried with some sample values in my local environment, and it is working without errors.

select @@version version_name

--ex:1
 declare @json nvarchar(max) = '{"time":"2015-04-29T07:12:20.9100000Z","callingimsi":"466920403025604","switch1":"China","switch2":"Germany"}'
 select @json as json_value
 where ISJSON(@json)=1

 --ex:2
 declare @json1 nvarchar(max) = 'abc'
 select @json1 as json_value1
 where ISJSON(@json1)=1

 --ex:3
 SELECT ISJSON('{"a":1}') as validate_json

CodePudding user response：

We've hit this same issue. ISJSON function works normally on Azure SQL databases, works normally on serverles SQL pool in Synapse, but fails in dedicated SQL pool in Synapse. Looks like MS updated the ISJSON function in Azure, but not all Azure SQL databases have the same version deployed.