Importing JSON files into SQL Server

I was assigned to a task to import a GeoJSON file into a SQL Server database. The file was pretty big; I couldn’t even open the file in an editor. At first, I wanted to write a simple program (with C# or Python) to open the file and then loop through the entries, parse each one of the entry and then save it into the database. But it would have taken time if I followed that process. All I wanted to do was save a JSON file right into the database. That’s where I gave OPENROWSET a try. Everything went well, and I successfully saved the file into the database:

DECLARE @GeoJSON nvarchar(max)

SELECT @GeoJSON = BulkColumn
 FROM OPENROWSET (BULK 'C:\areas.json', SINGLE_CLOB) as j

INSERT INTO dbo.Areas (Area,Coordinates)
SELECT
	Area,
	Coordinates
FROM
    OPENJSON(@GeoJSON, '$.features')
             WITH (
                 Area varchar(50) '$.properties.name',
                 Coordinates nvarchar(max) '$.geometry.coordinates' as JSON
                 )

As you might already know, the OPENROWSET is a table value function which returns a table with a single column called Bulk. What above code does is first open the JSON file and assign it to a variable called GeoJSON. Then I used another table value column called OPENJSON to parse the JSON. The first thing we can do is to set our root element, which is features in my case. Then elements can be referenced with the prefix $. There are two important things to mention. First, if you want to save a JSON element into a string column you will need to make sure the column type is nvarchar(max) and you add as JSON inside the WITH clause for that specific column.

You can also build a geography value if you have a column with that type:

DECLARE @GeoJSON nvarchar(max)

SELECT @GeoJSON = BulkColumn
 FROM OPENROWSET (BULK 'C:\areas.json', SINGLE_CLOB) as j

INSERT INTO dbo.Areas (Area,Coordinates)
SELECT
	Area,
	Coordinates,
    geography::STPointFromText('POINT (' + Longitude + ' ' + Latitude + ')', 4326) AS Geography,
    Longitude,
    Latitude
FROM
    OPENJSON(@GeoJSON, '$.features')
             WITH (
                 Area varchar(50) '$.properties.name',
                 Coordinates nvarchar(max) '$.geometry.coordinates' as JSON,
                 Longitude varchar(100) '$.lon',
                 Latitude varchar(100) '$.lat'
                 )