Import JSON data to SQL Server

In a previous article I demonstrated how you can import XML data using the OPENROWSET() function. In this article I’m going to show how you can do the same but with a JSON file and the OPENJSON() function.

Example JSON

[
    {
        "id": 1,
        "product_name": "white paint",
        "status": "in stock",
        "orders": [
            {
                "order_id": 1,
                "order_date": "2019-05-05",
                "price": 12.81
            },
            {
                "order_id": 2,
                "order_date": "2019-05-07",
                "price": 12.81
            }
        ]
    },
    {
        "id": 2,
        "product_name": "paint brush",
        "status": "in stock",
        "orders": [
            {
                "order_id": 3,
                "order_date": "2019-05-05",
                "price": 2.20
            },
            {
                "order_id": 4,
                "order_date": "2019-05-07",
                "price": 2.20
            }
        ]
    },
    {
        "id": 3,
        "product_name": "ready mix filler",
        "status": "out of stock",
        "orders": [
            {
                "order_id": 5,
                "order_date": "2019-05-07",
                "price": 3.99
            }
        ]
    }
]

Open Row Set

Firstly lets read the contents of the file into a variable.

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn 
FROM OPENROWSET (BULK 'C:\Temp\Example.json', SINGLE_CLOB) AS J

SELECT @JSON

Validate JSON

Next we want to validate that the data is in fact of type JSON before we try to process it. You can do this simply by using the ISJSON() function.

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn 
FROM OPENROWSET (BULK 'C:\Temp\Example.json', SINGLE_CLOB) AS J

IF ISJSON(@JSON) = 1
	PRINT 'My name is JSON'
ELSE
	PRINT 'My name is not JSON'

Open JSON

Finally we present the data into a tabular format by utilising the OPENJSON() function. In order to get nest arrays you use the CROSS APPLY join.

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn 
FROM OPENROWSET (BULK 'C:\Temp\Example.json', SINGLE_CLOB) AS J

IF ISJSON(@JSON) = 1
	BEGIN
		SELECT 
			 P.id			AS product_id
			,P.product_name	AS product_name
			,P.[status]		AS product_status
			,O.order_id		AS order_id
			,O.order_date	AS order_date
			,O.price		AS order_price
		FROM OPENJSON(@JSON)
		WITH (
			 id				varchar(100)
			,product_name	varchar(100)
			,[status]		varchar(100)
			,orders			nvarchar(max) as json
			) AS P
		CROSS APPLY OPENJSON(orders)
		WITH(
			 order_id		varchar(100)
			,order_date		varchar(100)
			,price			varchar(100)
			) AS O
	END

Once again you’ll notice that I’m not declaring the correct data types. This is because I like to handle bad data after staging it to a table.

Output of the OPENJSON function.

Hope this is helpful. Please feel free to comment below.