If you have a JSON document, there are several ways you could go about getting it into SQL Server.
If it’s a small document, you could copy and paste its contents. If it’s a larger document (or even a small one), you might want to import the whole file.
This article presents an example of importing a JSON file into a SQL Server database.
Selecting the Contents of the JSON File
T-SQL includes the OPENROWSET() function, that can read data from any file on the local drive or network, and return it as a row set. To do that, execute this function with the BULK option.
While this article was written specifically for importing the JSON file into a table, you can also use OPENROWSET() to read from a data file without necessarily loading it into a table.
This allows you to check the data first, before loading it into the table.
Here’s an example of selecting the contents of a JSON file.
SELECT BulkColumn FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB
) AS [Json];Result:
+--------------+
| BulkColumn |
|--------------|
| {
"pets" : {
"cats" : [
{ "id" : 1, "name" : "Fluffy", "sex" : "Female" },
{ "id" : 2, "name" : "Long Tail", "sex" : "Female" },
{ "id" : 3, "name" : "Scratch", "sex" : "Male" }
],
"dogs" : [
{ "id" : 1, "name" : "Fetch", "sex" : "Male" },
{ "id" : 2, "name" : "Fluffy", "sex" : "Male" },
{ "id" : 3, "name" : "Wag", "sex" : "Female" }
]
}
} |
+--------------+
In this case, the file system is Linux, and so the Linux path conventions are used when specifying which file to upload.
If you’re on Windows, your file path might look more like this one:
SELECT BulkColumn FROM OPENROWSET (
BULK 'D:\data\pets.json',
SINGLE_CLOB
) AS [Json];Anyway, we can see the contents of the JSON file above. Let’s now load it into a table.
Load it into a Table
We can modify the previous statement, so that the file’s contents are imported straight into a table.
-- Import it directly into the table
SELECT BulkColumn INTO ImportedJson FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB
) AS [Json];
-- Select the contents of the table
SELECT * FROM ImportedJson;Result:
+--------------+
| BulkColumn |
|--------------|
| {
"pets" : {
"cats" : [
{ "id" : 1, "name" : "Fluffy", "sex" : "Female" },
{ "id" : 2, "name" : "Long Tail", "sex" : "Female" },
{ "id" : 3, "name" : "Scratch", "sex" : "Male" }
],
"dogs" : [
{ "id" : 1, "name" : "Fetch", "sex" : "Male" },
{ "id" : 2, "name" : "Fluffy", "sex" : "Male" },
{ "id" : 3, "name" : "Wag", "sex" : "Female" }
]
}
} |
+--------------+
Doing that creates the table and inserts the JSON.
Note that when using OPENROWSET() with the BULK option, you must also provide a correlation name (also known as a range variable or alias) in the FROM clause.
If you don’t provide a correlation name, you’ll get an error.
In my example, I used Json as the correlation name, but feel free to choose your own.
Parse the JSON into Rows and Columns
This is where things get exciting. Not only can we upload the contents of a file and import it into a table column, we can also separate its contents across multiple rows and columns.
OPENJSON() is a table-valued function that converts JSON documents into a tabular format.
Therefore, we can use OPENJSON() to convert the contents of our JSON file into tabular format, and insert that into a table, or multiple tables if that’s the goal.
But again, we can check our data before inserting it into any tables.
-- Select the cats
SELECT Cats.* FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.cats' )
WITH (
CatId int '$.id',
CatName varchar(60) '$.name',
Sex varchar(6) '$.sex'
) AS [Cats]
-- Select the dogs
SELECT Dogs.* FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.dogs' )
WITH (
DogId int '$.id',
DogName varchar(60) '$.name',
Sex varchar(6) '$.sex'
) AS [Dogs]Result:
+---------+-----------+--------+ | CatId | CatName | Sex | |---------+-----------+--------| | 1 | Fluffy | Female | | 2 | Long Tail | Female | | 3 | Scratch | Male | +---------+-----------+--------+ (3 rows affected) +---------+-----------+--------+ | DogId | DogName | Sex | |---------+-----------+--------| | 1 | Fetch | Male | | 2 | Fluffy | Male | | 3 | Wag | Female | +---------+-----------+--------+ (3 rows affected)
This is exactly what it will look like once inserted into two tables.
To insert it into the tables, all we need to do is add INTO TableName between the SELECT part and FROM (where TableName is the name of the table we want to create).
-- Insert cats into a table
SELECT Cats.* INTO ImportedCats
FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.cats' )
WITH (
CatId int '$.id',
CatName varchar(60) '$.name',
Sex varchar(6) '$.sex'
) AS [Cats]
-- Insert dogs into a table
SELECT Dogs.* INTO ImportedDogs
FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.dogs' )
WITH (
DogId int '$.id',
DogName varchar(60) '$.name',
Sex varchar(6) '$.sex'
) AS [Dogs]
-- Select the results from both tables
SELECT * FROM ImportedCats
SELECT * FROM ImportedDogsResult:
+---------+-----------+--------+ | CatId | CatName | Sex | |---------+-----------+--------| | 1 | Fluffy | Female | | 2 | Long Tail | Female | | 3 | Scratch | Male | +---------+-----------+--------+ (3 rows affected) +---------+-----------+--------+ | DogId | DogName | Sex | |---------+-----------+--------| | 1 | Fetch | Male | | 2 | Fluffy | Male | | 3 | Wag | Female | +---------+-----------+--------+ (3 rows affected)
These tables were created using the column definitions we supplied in the WITH clause.
Each JSON key is mapped to a column name of our choosing.
You can also base your column names off of the key names in the JSON file. If you do that, you don’t need to map them with a path, as OPENJSON() will automatically match them with the JSON key names.
For example, instead of using the following WITH clause:
WITH (
DogId int '$.id',
DogName varchar(60) '$.name',
Sex varchar(6) '$.sex'
) AS [Dogs]You could use this:
WITH (
id int,
name varchar(60),
sex varchar(6)
) AS [Dogs]Load the JSON into a Variable
Another way of doing it would be to load the uploaded JSON into a variable, then passing that variable to the OPENJSON() function.
-- Declare variable
DECLARE @json nvarchar(max);
-- Upload JSON data into that variable
SELECT @json = BulkColumn FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB
) AS [Json];
-- Select the cats from that variable
SELECT * FROM OPENJSON(@json, '$.pets.cats')
WITH (
CatId int '$.id',
CatName varchar(60) '$.name',
Sex varchar(6) '$.sex'
);
-- Select the dogs from that variable
SELECT * FROM OPENJSON(@json, '$.pets.dogs')
WITH (
DogId int '$.id',
DogName varchar(60) '$.name',
Sex varchar(6) '$.sex'
);Result:
+---------+-----------+--------+ | CatId | CatName | Sex | |---------+-----------+--------| | 1 | Fluffy | Female | | 2 | Long Tail | Female | | 3 | Scratch | Male | +---------+-----------+--------+ (3 rows affected) +---------+-----------+--------+ | DogId | DogName | Sex | |---------+-----------+--------| | 1 | Fetch | Male | | 2 | Fluffy | Male | | 3 | Wag | Female | +---------+-----------+--------+ (3 rows affected)
Again, to insert this into a table, we would add INTO TableName after the SELECT part (where TableName is the name of the table you want to create).
Load a Whole Sub-Object into a Column
If you wanted whole sub-objects to reside in their own column, you can use the AS JSON option of the WITH clause.
For example, instead of having each cat and dog distributed across three columns, their whole JSON fragment could take up one column. Each animal will still have their own row.
Here’s an example of what I mean.
SELECT Cats.* FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.cats' )
WITH (
Cats nvarchar(max) '$' AS JSON
) AS [Cats]
SELECT Dogs.* FROM OPENROWSET (
BULK '/var/opt/mssql/bak/pets.json',
SINGLE_CLOB) AS [Json]
CROSS APPLY OPENJSON ( BulkColumn, '$.pets.dogs' )
WITH (
Dogs nvarchar(max) '$' AS JSON
) AS [Dogs]Result:
+------------------------------------------------------+
| Cats |
|------------------------------------------------------|
| { "id" : 1, "name" : "Fluffy", "sex" : "Female" } |
| { "id" : 2, "name" : "Long Tail", "sex" : "Female" } |
| { "id" : 3, "name" : "Scratch", "sex" : "Male" } |
+------------------------------------------------------+
(3 rows affected)
+-------------------------------------------------+
| Dogs |
|-------------------------------------------------|
| { "id" : 1, "name" : "Fetch", "sex" : "Male" } |
| { "id" : 2, "name" : "Fluffy", "sex" : "Male" } |
| { "id" : 3, "name" : "Wag", "sex" : "Female" } |
+-------------------------------------------------+
(3 rows affected)