JSON stands for JavaScript Object Notation. JSON is very lightweight popular textual data format. JSON is also used to store unstructured data in log files or NoSQL database.

JSON function in SQL Server enables you to combine NoSQL and relational database concept

By using SQL Server built-in functions and operators, you can do the following things with JSON text:

• Parse JSON text and read or modify values.
• Transform arrays of JSON objects into the table format.
• Run any Transact-SQL query on the converted JSON objects.
• Format the results of Transact-SQL queries in JSON format.

Read JSON text from JSON files

We normally receive JSON formatted files and require to read from SQL Server and store it in the SQL Server Tables.

The files are stored on local shared drives with “.json” extension. Read the content of a JSON file using OPENROWSET SQL table-valued function

SELECT *
FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\test1.json', SINGLE_CLOB) as j

 

 

 

Load JSON File content into a variable. (JSON content will be truncated after the variable length)

DECLARE @json AS NVARCHAR(MAX)
SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\test1.json', SINGLE_CLOB) as j

SELECT @json

Validate JSON Document for valid JSON

DECLARE @json AS NVARCHAR(MAX)
SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\test1.json', SINGLE_CLOB) as j

SELECT IsJSON(@json)

Returns 1 if the string contains valid JSON; otherwise, returns 0. Returns null if the expression is null.