This article is half-done without your Comment! *** Please share your thoughts via Comment ***
JSON + RDBMS is one of the interesting topics, in the new version of SQL Server 2016 we can store JSON formatted data.
Now a day JSON data type is a standard feature for all new coming RDBMS product versions like PostgreSQL 9.4, and MySQL 5.7 have already introduced this.
Now We can say, we have SQL Server 2016 with the RDBMS + NoSQL concepts.
We can store JSON data as nvarchar, but the JSON related functions have the advantage of enforcing that each stored value is valid according to the JSON rules.
Small demonstration on SQL Server 2016 JSON:
Create a sample table and define two column as NVARCHAR to store JSON data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_TestSQLJSON ( ID INTEGER PRIMARY KEY ,DepartmentName VARCHAR(250) ,EmployeeName NVARCHAR(MAX) ,EmployeeAddress NVARCHAR(MAX) ) GO |
Use ISJSON() and add check constraint in json columns:
If your json format and document is correct, It returns true other It returns false.
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE tbl_TestSQLJSON ADD CONSTRAINT ck_tbl_TestSQLJSON_EmployeeName CHECK (ISJSON( EmployeeName ) > 0) GO ALTER TABLE tbl_TestSQLJSON ADD CONSTRAINT ck_tbl_TestSQLJSON_EmployeeAddress CHECK (ISJSON( EmployeeAddress ) > 0) GO |
Insert few sample JSON data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
INSERT INTO tbl_TestSQLJSON VALUES ( 1 ,'Sales' ,N'{"firstName": "Anvesh", "lastName": "Patel"}' ,N' {"address" : { "India": "Hyderabad" ,"USA": "Newyork" } }' ) ,( 2 ,'Production' ,N'{"firstName": "Neevan", "lastName": "Patel"}' ,N' {"address" : { "India": "Ahmedabad" ,"USA": "Washington DC" } }' ) ,( 3 ,'Animation' ,N'{"firstName": "Eric", "lastName": "Lorn"}' ,N' {"address" : { "India": "Mumbai" ,"USA": "Chicago" } }' ) GO |
SELECT json data:
|
1 2 3 4 5 6 7 8 9 |
SELECT ID ,DepartmentName ,JSON_VALUE(EmployeeName, '$.firstName') AS EmployeeFirstName ,JSON_VALUE(EmployeeAddress, '$.address.India') AS AddressIndia ,JSON_VALUE(EmployeeAddress, '$.address.USA') AS AddressUSA ,JSON_QUERY(EmployeeAddress, '$.address') AS EmployeeAddressJSON FROM tbl_TestSQLJSON GO |
Use JSON_VALUE() in WHERE Clause:
|
1 2 3 4 5 6 7 |
SELECT ID ,DepartmentName ,JSON_VALUE(EmployeeName, '$.firstName') AS EmployeeFirstName FROM tbl_TestSQLJSON WHERE JSON_VALUE(EmployeeName, '$.firstName') = 'Anvesh' GO |
