Json in SQL Server - Load and convert Json data

Data is the backbone of the business, and it can be in any sort of format. It can be lying in your relational databases or it can be a tweet in the twitter or a post in the Facebook or a message in the Instagram. Worldwide data is expected to hit 175 zettabytes by 2025 and over 80% of this data will be in an unstructured format due to social media and mobile applications. 

Unstructured data usually refers to information that doesn't reside in a traditional row-column database. For example, data stored in XML and JSON documents, CSV files, and Excel files are all unstructured. RDBMSs like Oracle SQL*Plus, Microsoft SQL Server, MySQL are adopting the changes and introducing the functionalities that can handle unstructured data.

My recent articles focused on such newly introduced functions that either helps in analyzing the data, importing or converting the unstructured data into a readable and meaningful format.

In this article, we will see how a Json formatted file be converted into usual row-column table format in SQL Server 2016 using OpenJson function. Json (JavaScript Object Notation) is open standard file format that consists of attribute-value pairs and array data types. Look at the below example.

Filename: “test.json”

[{"Name":"Zafar Iqbal","surname": "Farooq", "Age":30, "Address":["142", "Street 29c", "Rashidiya", "Dubai"]},

{"Name":"Tahir","surname": "Farooq", "Age":24, "Address":["12C", "Happy Street", "Al Quoz", "Dubai"]}]

 

DECLARE @Json VARCHAR(MAX)
SELECT @Json = BulkColumn
FROM OPENROWSET (BULK 'D:\MyDocuments\blog posts\JSON-SQLServer\test.json', SINGLE_CLOB) AS j 

SELECT * FROM OPENJSON (@Json)
     WITH(    Name VARCHAR(20) '$.Name',
             LastName VARCHAR(20) '$.surname',
              Age INT '$.Age',
              Building VARCHAR(10) '$.Address[0]',
              Street VARCHAR(20) '$.Address[1]',
              Area VARCHAR(20) '$.Address[2]',
              City VARCHAR(20) '$.Address[3]')

Explanation:

• In the above code, we are importing the “test.json” contents into a variable named @Json.
   
• OPENROWSET function is invoked to read the data from a file. Since it is a table-valued function, it returns a table with a single column and the column name must be “BulkColumn”.
  
  
• SINGLE_CLOB reads a file as VARCHAR(MAX).
  
  
• “As” in the above code is to provide a correlation name for the bulk rowset in the from clause. The alias name can be anything.
  
  
• Once we populated the json data into a variable, we can proceed further to convert the data into rows and columns by parsing it using OpenJSON function. OpenJSON function accepts JSON as a parameter and it returns the key:value pairs of the first-level elements in the JSON.
  
  
• Address field in the Json is an array data type consists of building number, street, area and city which we split during the query and the output is –
  

This can be achieved using JSON_VALUE for primitive and JSON_QUERY for Array datatypes for a row.

DECLARE @json NVARCHAR(MAX)

SET @json='{"Name":"Zafar Iqbal","surname": "Farooq", "Age":30, "Address":["142", "Street 29c", "Rashidiya", "Dubai"]}';

SELECT

    JSON_VALUE(@json, '$.Name') AS Name,

    JSON_VALUE(@json, '$.surname') AS Lastname,

    JSON_VALUE(@json, '$.Age') AS Age,

    JSON_QUERY(@json, '$.Address') AS Address,

    JSON_VALUE(@json, '$.Address[0]') AS BuildingNumber,

    JSON_VALUE(@json, '$.Address[1]') AS Street,

    JSON_VALUE(@json, '$.Address[2]') AS Area,

    JSON_VALUE(@json, '$.Address[3]') AS City