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.