Lesson Learned #34: Does Azure SQL Database support OPENJSON?


The answer is Yes! but your database needs to have in the compatibility level 130 or above. If your database compatibility level is less than 130 you could get an error about "incorrect syntax near the keywork with" or "invalid syntax".

 

Please, follow these steps to read a JSON file from a Azure Blob Storage from SQL Server Management Studio:

  1. Download the latest version of SQL Server Management Studio.
  2. Connect to your Azure SQL Database and run the following TSQL in order to identify the compatibility level version of your database: select * from sys.databases
  3. If your database has the compatibility level than 130 you need to change it, if not you will not be able to use OPENJSON. We need to change running the following TSQL command: ALTER DATABASE DotNetExample SET COMPATIBILITY_LEVEL = 130. Remember that Azure SQL Database supports right now the compatibility level of 140 ( SQL SERVER 2017 ).
  4. Create your JSON file and upload it to the Azure Blob Storage. You could use, for example, Microsoft Azure Storage Explorer
  5. Give the permissions to read data in the Azure Blob Storage and copy the key.
  6. Using SQL Server Management Studio:

 

Create a new credential:

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2017-04-17&ss=bfqt&srt=sco&sp=rwdl&st=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

 

Create a new external data.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://xxxxx.blob.core.windows.net',
CREDENTIAL= MyAzureBlobStorageCredential);

 

Run the following TSQL to read the data from a JSON file. (In my case, I created a JSON with the results of sys.dm_db_resource_stats execution)

SELECT book.* FROM OPENROWSET (BULK 'ResourceDB.json',DATA_SOURCE = 'MyAzureBlobStorage',  SINGLE_cLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( [end_time] datetime , [avg_cpu_percent] [decimal](5, 2), [avg_data_io_percent] [decimal](5, 2) , [avg_log_write_percent] [decimal](5, 2) , [avg_memory_usage_percent] [decimal](5, 2), [xtp_storage_percent] [decimal](5, 2), [max_worker_percent] [decimal](5, 2) , [max_session_percent] [decimal](5, 2) , [dtu_limit] [int] ) AS book

Enjoy!

Comments (0)

Skip to main content