Written by 
Dedicated SQL pools? Yes, I know, most of my recent posts have been on Microsoft Fabric topics and its been a while since I did a post on Azure Synapse dedicated pools. However, I was speaking to a few people lately whilst working on a project (and of course this project is using dedicated SQL pools) and we were discussing reading JSON files, I mentioned it was something you could do directly from within a dedicated pool. So, figured I might as well do a little tiny blog post with some code, Azure Synapse dedicated pools – we still love you despite Fabric stealing all your glory 😍
The good thing about this technique is that you don’t need to know the schema before you read the file, unlike with CSV files. I just wish you could use AUTO_CREATE_TABLE with CSV files, like you can with parquet files. Anyway, back to the subject in hand… suppose we have a JSON file with the following contents…
someParentFieldInTheJSONWithChildElements : {
someProperty: 1234,
fields: [{
name: "john",
type: "something"
},{
name: "jane",
type: "another"
}]
}
We can read this and do some basic parsing with this little bit of example code…
-- First create a temp table to read the JSON into, we'll read the file content into a single column
DROP TABLE IF EXISTS [#mytable];
CREATE TABLE [#mytable] (
[FileContent] NVARCHAR(MAX) NOT NULL
WITH (
DISTRIBUTION = ROUND_ROBIN
,HEAP
)
-- Now use 'COPY INTO' to read the JSON data into the temp table (using CSV load 'hack' settings to essentially read in as one big string)
COPY INTO
[#mytable]
FROM -- Don't forget to replace this with a real storage account location!
'https://somestorageaccountname.dfs.core.windows.net/somecontainer/somefolder'
WITH (
FILE_TYPE = 'CSV'
,FIELDTERMINATOR = '0x0b'
,FIELDQUOTE = '0x0b'
,ROWTERMINATOR = '0x0d'
)
/*
Example - read some JSON values, with some structure like:
{
"someParentFieldInTheJSONWithChildElements" : {
"someProperty": 1234,
"fields": [{
"name": "SomeField",
"type": "varchar"
},{
"name": "OtherField",
"type": "int"
}]
}
}
*/
-- First read in the JSON content ready for further parsing, here we just essentially read the root node from the data so we don't have to keep specifying it everytime later on ;-)
DECLARE @jsonContent NVARCHAR(MAX)
SELECT
@jsonContent = JSON_VALUE([FileContent], '$.someParentFieldInTheJSONWithChildElements')
FROM
[#mytable]
-- Now query the JSON to get some values
SELECT
[cols].[ColumnName]
,[cols].[DataType]
FROM -- Read in the 'fields' element
OPENJSON(@jsonContent) WITH (
[Columns] NVARCHAR(MAX) '$.fields' AS JSON
) [f]
OUTER APPLY -- 'Map' each of the elements in the 'fields' array
OPENJSON([f].[Columns]) WITH (
[ColumnName] NVARCHAR(128) '$.name'
,[DataType] NVARCHAR(128) '$.type'
) [cols]
-- Do whatever else with it...
-- Drop temp table when done
DROP TABLE IF EXISTS [#mytable];
Anyway, that’s all for today. Who knows, maybe someone might find this useful, have a good day…