Is It Possible to Parse JSON Data in an External Table with Serverless SQL Pool?

Question

Hello,

I’m currently working on creating an external table in Azure Synapse Serverless SQL Pool using a JSON Lines formatted file. However, I’ve encountered a limitation: Serverless SQL Pool does not natively support JSON file formats for external table creation.

SQL Script Used:

Below is the script I used to define the external table.

-- create external data source
CREATE EXTERNAL DATA SOURCE LogsDataSource
WITH (
  LOCATION = 'https://<my_storage_account>.blob.core.windows.net'
);


-- create external data format
CREATE EXTERNAL FILE FORMAT JsonLinesFormat
WITH (
  FORMAT_TYPE = DELIMITEDTEXT,
  FORMAT_OPTIONS (
    FIELD_TERMINATOR = '0x0',
    ENCODING = 'UTF8'
  )
);

-- Create the external table with a single column for each json line
CREATE EXTERNAL TABLE MyExternalTable
(
  Line NVARCHAR(MAX),
)
WITH (
  LOCATION = '/<my_container>/...../LOGGING/y=2025/m=02/d=19/h=16/m=00/PT1H.json', 
  DATA_SOURCE = LogsDataSource,
  FILE_FORMAT = JsonLinesFormat
);

-- Query the external table to get the logs
SELECT TOP 3 * FROM MyExternalTable;

One-column Output:

With this approach, the external table contains only one column, where each row represents a full JSON line from the file. Bellow is an example of my JSON file structure.

My Json file example:

{ "time": "2025-03-07T17:00:00.0491935Z", "resultDescription": ".. some text ..", ... more key:value pairs }
{ "time": "2025-03-07T17:00:00.0492294Z", "resultDescription": ".. some text ..", ... more key:value pairs }

Each row in the external table corresponds to a complete JSON object, but I need to extract specific fields into separate columns for easier querying..

Desired Output

I would like to modify the external table so that it has two separate columns:

1. time → Extracted timestamp from the JSON object.
2. **Line**→ The entire JSON object as a string (or, if possible, the second JSON key: resultDescription).

I tried defining the external table with two columns to map JSON keys directly, but the approach was unsuccessful:

-- Attempted external table definition
CREATE EXTERNAL TABLE MyExternalTable
(
  time NVARCHAR(255),
  resultDescription NVARCHAR(MAX)
)
WITH (
  LOCATION = '/<my_container>/...../LOGGING/y=2025/m=02/d=19/h=16/m=00/PT1H.json', 
  DATA_SOURCE = LogsDataSource,
  FILE_FORMAT = JsonLinesFormat
);

Unfortunately, this does not work because Serverless SQL Pool does not support defining individual JSON keys as columns in external tables.

Question:
Is it possible to achieve this structure using Serverless SQL Pool? If so, what would be the best approach to parse the JSON and create a multi-column external table?

Any guidance would be greatly appreciated!

Answer 1

You can follow the below method :

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

except those are not via external tables as extrenal table wont support JSON extension