JSON refers to Javascript Object Notation. It is a popular text data format used to exchange data on modern web and mobile applications. It is based on a subset of the Javascript programming language. It is used to store random data in log files or NoSQL.
It also enables us to integrate NoSQL and related concepts into the same database.
Advantage of JSON data:
- Lightweight data-interchange format.
- Easy for humans to read and write.
- Easy for machines to parse and generate.
JSON data using built-in functions and operators:
- We can parse the JSON text and read or modify the values.
- Transform JSON objects into table format.
- We can run any Transact-SQL query on the converted JSON objects.
- Transform the results of Transact-SQL queries back to JSON format.
| Function | Description |
|---|---|
| ISJSON | It tests whether a string contains JSON data or not |
| JSON_VALUE | It extracts a scalar value from a JSON string. |
| JSON_QUERY | It extracts an object or an array from a JSON string. |
| JSON_MODIFY | It changes a value in a JSON string. |
Now, to import JSON data in the SQL server, we will use OPENROWSET (BULK).
Step 1: Use of OPENROWSET(BULK)
It is a table-valued function that can read data from any file.. It returns a table with a single column that contains all the contents of the file. It can just load the entire contents of a file as a text value. (This single large value is known as a single character large object or SINGLE_CLOB.)
Syntax:
SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;
It reads the content of the file and returns it in BulkColumn. The correlation name must be specified. We have the JSON file named “file1.json”. Content of JSON file:
Query:
[{"Firstname": "ROMY", "Lastname": "KUMARI", "Gender": "female", "AGE" : 22 },
{"Firstname": "PUSHKAR", "Lastname": "JHA", "Gender": "male", "AGE" : 22 },
{"Firstname": "SHALINI", "Lastname": "JHA", "Gender": "female", "AGE" : 21 },
{"Firstname": "SAMBHAVI", "Lastname": "JHA", "Gender": "female", "AGE" : 18 } ]Step 2: Import file1.json into SQL server
Query:
SELECT * FROM OPENROWSET (BULK 'E:\file1.json', Single_CLOB) AS import;
Output:
The entire content is returned as a single column.
Step 3: Convert JSON data
- Declare a variable.
- Store data of Bulkcolumn in a variable.
- Use OPENJSON() function for converting the JSON output from a variable into a tabular format.
- WITH clause along with the column definition (key should be used as column name).
Query:
Declare @JSON varchar(max) SELECT @JSON=BulkColumn FROM OPENROWSET (BULK 'E:\file1.json', SINGLE_CLOB) import SELECT * FROM OPENJSON (@JSON) WITH ( [Firstname] varchar(20), [Lastname] varchar(20), [Gender] varchar(20), [AGE] int );
Output: