Regarding my previous post: https://powerplatform.fi/how-to-read-a-csv-file-from-sharepoint-to-azure-sql-with-power-automate/ I got a question about how to read a JSON (not csv!) from Azure Blob Storage to Azure SQL. And yes, this is perfectly doable with the same approach.
Here’s an example of the SQL statement that you can use when handling JSON files:
INSERT INTO account
([name], [accountid], [city])
SELECT
[name], [accountid], [city]
FROM OPENROWSET(BULK ‘accounts.json’, CODEPAGE = ‘65001’, DATA_SOURCE = ‘csvStorage’, SINGLE_CLOB) as json
CROSS APPLY OPENJSON(BulkColumn)
WITH (
[name] nvarchar(300) ‘$.name’,
[accountid] nvarchar(300) ‘$.accountid’,
[city] nvarchar(300) ‘$.address1_city’
) as [JSONDATA];
There might be simpler alternatives to this approach though where you do not need the Blob Storage:
Also, this is super interesting (don’t have tried it yet) – how to connect Azure SQL directly to REST API: