Open row source

You'll need to use the built-in serverless SQL pool or a dedicated SQL pool to query JSON files directly from Azure Data Lake Storage or Azure Blob Storage.

Upload your JSON file to a storage account linked to your Synapse workspace.

You can query the JSON file directly using the OPENROWSET function in a Synapse SQL query.

SELECT *

FROM OPENROWSET(

    BULK 'https://<storageaccount>.blob.core.windows.net/<container>/<path_to_json_file>.json',

    FORMAT='SINGLE_CLOB'

) AS json_file

CROSS APPLY OPENJSON(BulkColumn)

WITH (

    id INT '$.id',

    customer_id INT '$.customer_id',

    DEBUG_ASYNC VARCHAR(100) '$.data.DEBUG_ASYNC',

    DEBUG_RATING VARCHAR(100) '$.data.DEBUG_RATING',

    currency VARCHAR(10) '$.data.al_avn_52_limit.currency',

    limit FLOAT '$.data.al_avn_52_limit.limit',

    al_brokerage_hull FLOAT '$.data.al_brokerage_hull',

    al_brokerage_liab FLOAT '$.data.al_brokerage_liab',

    off_rate FLOAT '$.data.al_cargo.off_rate',

    own_rate FLOAT '$.data.al_cargo.own_rate',

    value FLOAT '$.data.al_cargo.value',

    perc_calculated FLOAT '$.data.al_dep_min_prem_dep.perc.calculated',

    perc_selected FLOAT '$.data.al_dep_min_prem_dep.perc.selected',

    value_calculated FLOAT '$.data.al_dep_min_prem_dep.value.calculated',

    value_selected FLOAT '$.data.al_dep_min_prem_dep.value.selected',

    min_prem_est FLOAT '$.data.al_dep_min_prem_est.value',

    min_prem_min_calculated FLOAT '$.data.al_dep_min_prem_min.value.calculated',

    min_prem_min_selected FLOAT '$.data.al_dep_min_prem_min.value.selected'

// complete the other fields

) AS json_data;

• OPENROWSET: Reads the file as a single string (SINGLE_CLOB).
• OPENJSON: Parses the JSON string and allows you to extract individual elements.
• CROSS APPLY: Applies the OPENJSON function to the rows returned by OPENROWSET.

Hi Vineet S ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

To read the JSON files, The following sample query reads JSON and line-delimited JSON files, and returns every document as a separate row.

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

The JSON document in the preceding sample query includes an array of objects. The query returns each object as a separate row in the result set. Make sure that you can access this file. If your file is protected with SAS key or custom identity, you would need to set up server level credential for sql login.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou