Access to JSON file content using T-SQL

access-json-file.sql

/*
	Create the Database Master Key, if needed
*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My-L0ng&Str0ng_P4ss0wrd!';
GO

/*
	Create database scoped credentials to store the Shared Access Signature (SAS) 
	needed to access the Azure Blob Container. More info on Azure Blob SAS here:
	
	https://docs.microsoft.com/en-us/azure/storage/storage-dotnet-shared-access-signature-part-1

	SAS can be created right from Azure Portal or Azure CLI
*/

CREATE DATABASE SCOPED CREDENTIAL [Azure-Storage-Credentials]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<your SAS signature, WITHOUT any leading "?">';
GO

/*
	Now create the external data source and point it to the Azure Blob Store
*/
CREATE EXTERNAL DATA SOURCE [Azure-Storage]
WITH 
( 
	TYPE = BLOB_STORAGE,
 	LOCATION = 'https://<storage-account>.blob.core.windows.net',
 	CREDENTIAL= [Azure-Storage-Credentials]
);
GO

/*
	Access the file content via SINGLE_BLOB BULK OPENROWSET
	and pass it to OPENJSON
*/
WITH cte AS 
(
	SELECT 
		CAST(BulkColumn AS NVARCHAR(MAX)) AS JsonData 
	FROM 
		OPENROWSET(BULK '<container>/<blob>', DATA_SOURCE = 'Azure-Storage', SINGLE_CLOB) AS AzureBlob
)
SELECT
	j.*
FROM
	cte
CROSS APPLY 
	OPENJSON(cte.JsonData) j
go