Detailed Explanation

When using the OPENROWSET function in Azure Synapse Analytics to read JSON files from Azure Data Lake Storage, the correct FORMAT option is CSV (Option D), not JSON (Option A).

Why CSV is the Correct Choice:

1. OPENROWSET Function Limitations: The OPENROWSET function in Azure Synapse Analytics does not have a native 'JSON' format option. The supported format values are:

   • 'CSV' for delimited text files
   • 'PARQUET' for Parquet files
   • 'DELTA' for Delta Lake format

2. JSON File Processing Workaround: To read JSON files using OPENROWSET, you must use the CSV format with specific delimiter configurations:

   • Set FORMAT = 'CSV'
   • Configure FIELDTERMINATOR = '0x0b' (vertical tab character)
   • Configure FIELDQUOTE = '0x0b'
   • For multi-line JSON files, also set ROWTERMINATOR = '0x0b'

3. Achieving Single JSON Record per Rowset: This configuration ensures that each JSON document is treated as a single field/row, meeting the requirement that "each rowset contains a single JSON record."

4. Microsoft Documentation Support: Official Microsoft documentation explicitly states that JSON files should be read using the CSV format with the specified delimiter settings.

Why Other Options Are Incorrect:

• Option A (JSON): This format is not supported by the OPENROWSET function in Azure Synapse Analytics and will result in an error.
• Option B (DELTA): This is for Delta Lake format files, not JSON files.
• Option C (PARQUET): This is for Parquet binary files, not JSON text files.

Recommended Implementation:

SELECT doc
FROM OPENROWSET(
    BULK 'https://storage1.dfs.core.windows.net/container1/folder1/*.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0b'
) WITH (doc NVARCHAR(MAX)) AS rows

SELECT doc
FROM OPENROWSET(
    BULK 'https://storage1.dfs.core.windows.net/container1/folder1/*.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0b'
) WITH (doc NVARCHAR(MAX)) AS rows

This approach correctly processes JSON files while ensuring each rowset contains a single JSON record as required.