Create table with JSON type in SQL Server 2016.

How to create a table that could store JSON in SQL Server 2016?

Or

Create table with Json type in SQL Server in 2016.

There is no Json data type in SQL server 2016, if you will try to create you will get,
Column, parameter, or variable #4: Cannot find data type json.

But yes you can treat any column as JSON by JSON_QUERY , or parse data as Json using parseJSON which now supported in SQL server 2016.

create database DB2016 --create database

use DB2016 --use created database

create TABLE dbo.Employees --create table
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Email varchar(100),
  Mobile varchar(15),
  Data NVarChar(max) --JSON data column
)

How to insert Json Data in Sql Server 2016?

By using simple Insert statement you could do that.

Insert into Employees (Email, Mobile, Data) values('raj@aapc.com', '8800282570', '{      "name": "Raj",      "skills": ["SQL", "C#", "JS"]  ,"Age": 29  }')

Insert into Employees (Email, Mobile, Data) values('arun@aapc.com', '8800282570', '{  "name": "Arun",  "address":   {   "pin": 110092,   "home": "12-S",   "City": "Delhi"   }  }')

Insert into Employees (Email, Mobile, Data) values('Raja Babu', '7070704707', '{ "name": "Raja babu", "skills": ["SQL", "C#", "JS"], "Age": 24 }')

How to select Json data from table in sql server 2016?

1.

select * from employees

2.
select id,email, mobile, JSON_VALUE( data, '$.name') as Name from dbo.Employees;

3.

select id,email, mobile,
JSON_VALUE( data, '$.name') as Name,
JSON_VALUE( data, '$.age') as Age -- Age != age
from dbo.Employees;

4.

select id,email, mobile, JSON_QUERY( data, '$') as Name from dbo.Employees;

select id,email, mobile, JSON_QUERY( data, '$.address') as data from dbo.Employees;

5.

select * from Employees where id=1 for json auto ;
select * from Employees for json path;

How to parse JSON data and store in to variable?

DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
       { "id" : 2,"info": { "name": "Raj", "surname": "Thakur" }, "age": 28 },
       { "id" : 5,"info": { "name": "Raja", "surname": "Babu" }, "dob": "1996-08-07" }
 ]'

SELECT *
FROM OPENJSON(@json)
  WITH (id int 'strict $.id',
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',
        age int, dob datetime2 '$.dob')

Nested Complex Data in tabular format in JSON Query in SQL server 

DECLARE @json NVARCHAR(1000)
SELECT @json =
N'{
  "OrderInfo": [
    {
      "OrderID": 111,
      "CustomerID": 1212,

      "OrderDetail": [
        {
  "ProductName": "P Ball",
          "ProductID": 56,
          "UnitPrice": 5656     
        },     
        {         
  "ProductName": "C Dall", 
          "ProductID": 54,
          "UnitPrice": 1222
        },
        {     
  "ProductName": "X Pen",       
          "ProductID": 33,
          "UnitPrice": 233
        }
      ]
    }
  ]
}'

SELECT

    JSON_Value (c.value, '$.OrderID') as OrderID,
    JSON_Value (c.value, '$.CustomerID') as CustomerID,
JSON_VALUE (p.value, '$.ProductName') as ProductName,
    JSON_Value (p.value, '$.ProductID') as ProductID,
    JSON_Value (p.value, '$.UnitPrice') as UnitPrice

FROM OPENJSON (@json, '$.OrderInfo') as c
CROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p