Reading JSON Files from disk

G’day,

Data comes in a variety of formats one of which is JSON, and often the source would be files that reside on the operating system.

We have a few different options to read these files into SQL Server, often SSIS is the one that springs to mind.

But we do have another option, one that is simpler than SSIS and uses just a language that we’re already familiar with T-SQL.

Lets, take a look.

Supposing we have the following JSON in a file (I’ve slimmed this down for display purposes – there’s a longer file in the Resources)

[
    {
        "configuration_id": 101,
        "name": "recovery interval (min)",
        "value": 0,
        "minimum": 0,
        "maximum": 32767,
        "value_in_use": 0,
        "description": "Maximum recovery interval in minutes",
        "is_dynamic": true,
        "is_advanced": true
    },
    {
        "configuration_id": 102,
        "name": "allow updates",
        "value": 0,
        "minimum": 0,
        "maximum": 1,
        "value_in_use": 0,
        "description": "Allow updates to system tables",
        "is_dynamic": true,
        "is_advanced": false
    }
]

Then we can simple load that file into a T-SQL variable using the following code (note – your path may vary)

USE tempdb;
GO

DECLARE @JSON NVARCHAR(MAX);

SELECT 
	@JSON = BulkColumn
FROM 
	OPENROWSET (BULK 'C:\data-marty\JSON\Reading JSON Files from disk\Reading_JSON_Files_from_disk.json', SINGLE_CLOB) as j;

SELECT @JSON AS [JSON];
GO

If we are using SQL Server Management Studio (SSMS) then the contents of the files are shown in a single column

This is somewhat ugly and the column is not recognised as JSON (as we’ve already seen there’s no JSON datatype in SQL Server)

So, if we want to see this in a nice JSON editor, then we can simply copy the output cell and paste the text into an online JSON editor / formatter / parser and ask to see it formatted nicely.

Some online JSON resources that I have found useful are

However, you could just use Visual Studio Code.

The same is true in Azure Data Studio.

And that’s simply because the environment has no clue what type of data is being returned – if it did, things might be different, as we’ll see in a later post.

And while it might be irrelevant to the tool (SSMS or ADS) exactly what sort of data has been brought back from the statement – it should be very relevant to the author of the script.

We’re already aware that there is no JSON data type in SQL Server. But we do have the ability to check that we have valid JSON.

So, let’s do that.

DECLARE @JSON NVARCHAR(MAX);

SELECT 
	@JSON = BulkColumn
FROM 
	OPENROWSET (BULK 'C:\data-marty\JSON\Reading_JSON_Files_from_disk\Reading_JSON_Files_from_disk_01.json', SINGLE_CLOB) as j;

SELECT ISJSON(@JSON);

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty

Download Files

Reading JSON Files from disk

Leave a Reply

Your email address will not be published. Required fields are marked *