Handling JSON with SQL Server

JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications.

I have a sample JSON file contains Multi-language data. How to read it from the JSON file in SQL Server ?

File Name : one.json

here we go....

In SQL Server 2016, There is an option to consume JSON file content using : OPENJSON

DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn FROM OPENROWSET (BULK 'C:\Personal\one.json', SINGLE_CLOB) j

SELECT * FROM OPENJSON(@json) 
WITH 
(
id int 'strict $.id',
English varchar(50) '$.language.english',
Tamil nvarchar(50) '$.language.tamil',
Telugu nvarchar(50) '$.language.telugu'
)

But, The result I got was..

It says, Multi-Language data has not been parsed when read it from the file.

According to MSDN, SINGLE_CLOB - Reads the content as ASCII. But we need to read as Unicode data

So, The following option used SINGLE_NCLOB which reads the content in Unicode

DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn FROM OPENROWSET (BULK 'C:\Personal\one.json', SINGLE_NCLOB) j

SELECT * FROM OPENJSON(@json) 
WITH 
(
id int 'strict $.id',
English varchar(50) '$.language.english',
Tamil nvarchar(50) '$.language.tamil',
Telugu nvarchar(50) '$.language.telugu'
)

The result was different.... I got an Error
Msg 13609, Level 16, State 4, Line 4
JSON text is not properly formatted. Unexpected character '⁛' is found at position 0.

What next ?

Let us see, How the JSON file was saved/used the Encoding ? Yes. It was UTF-8 :)

The File Encoding changed and saved it again as Unicode

  

And, I tried again...

DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn FROM OPENROWSET (BULK 'C:\Personal\one.json', SINGLE_NCLOB) j

SELECT * FROM OPENJSON(@json) 
WITH 
(
id int 'strict $.id',
English varchar(50) '$.language.english',
Tamil nvarchar(50) '$.language.tamil',
Telugu nvarchar(50) '$.language.telugu'
)

Yes. I got it now