JSON_VALUE on Azure Sql Data Warehouse : 'JSON_VALUE' is not a recognized built-in function name

Question

JSON_VALUE() does not work on Azure SQL Data Warehouse even if the compatibility level is 130.

Despite having the compatility_level set to 130, it seems that I cannot have the JSON_VALUE working on Azure SQL Data Warehouse. ref: https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-2017

CREATE TABLE dbo.test (
    doc_name        VARCHAR(255) NOT NULL,
    code            VARCHAR(80) NOT NULL,
    currentState_id INT NULL,
    json_doc        NVARCHAR(MAX)  null ) WITH (
    DISTRIBUTION = HASH ( doc_name ), 
    CLUSTERED INDEX ( doc_name ASC ) );

insert into dbo.test ([doc_name],[code],[currentState_id],[json_doc])  values ('doc1.txt','doc1',1,'{"one":"value1","two":"value2"}');

select JSON_VALUE(json_doc,'$.one') from dbo.test where code = 'doc1';
--  Msg 104162, Level 16, State 1, Line 17 
-- 'JSON_VALUE' is not a recognized built-in function name. SELECT compatibility_level FROM SYS.databases where name = my_DW';
-- returns 130

From what I understand, according to the documentation, JSON_VALUE should work on Azure SQL Data Warehouse if the compatibility level is 130.

So switched to a database that has compatibility_level to 140 and it worked. However, can't change the compatibility level of my database to 140. "ALTER DATABASE [my_DW] SET COMPATIBILITY_LEVEL = 140 GO", returns Parse error at line: 2, column: 27: Incorrect syntax near '140'. — Simon Vaillancourt
It’s supposed to be available in preview now. Maybe open an Azure support case? azure.microsoft.com/en-us/blog/… — GregGalloway
@GregGalloway Your comment and the link provided was what I needed to find the cause of my problem. Running SELECT @@VERSION AS 'SQL Data Warehouse'; revealed that my data warehouse is still in Microsoft Azure SQL Data Warehouse - 10.0.9999.0 Apr 9 2019 06:29:14 Copyright (c) Microsoft Corporation. — Simon Vaillancourt
I would be curious if pause and resume would get you the latest build — GregGalloway
The JSON functions worked for me today with a warehouse I created last month. Version number the same, but different date!? Microsoft Azure SQL Data Warehouse - 10.0.9999.0 May 30 2019. — wBob

wBob wBob · Accepted Answer · 2019-07-24T10:53:51

As at 8th May 2019, JSON_VALUE does work in Azure SQL Data Warehouse

See the main announcement here.

My results from a live test today with your query in Azure SQL Data Warehouse:

JSON_VALUE on Azure Sql Data Warehouse : 'JSON_VALUE' is not a recognized built-in function name

1 Answers