JSON Data in SQL Server

The write way to declare JSON column
- min NVARCHAR(4000) is required to identify as JSON DATA

CREATE TABLE BLOG
(
    blogID  INT                   IDENTITY(1,1),
    blogTitle  NVARCHAR(100),
    blogText  NVARCHAR(MAX),
    blogDetails    NVARCHAR(4000) 
    CONSTRAINT pk_blog_blodID             PRIMARY KEY(blogID),
    CONSTRAINT chk_blog_blogDetails CHECK(ISJSON(blogDetails) = 1 )
)

JSON_VALUE(<field>,<path>)

JSON_QUERY(<field>,<path>)

JSON_MODIFY(<field>,<path>,<new value>)

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
— returns a table with ‘BulkColumn’ field

OPENJSON & CROSSAPPLY

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
CROSS APPLY OPENJSON(BulkColumn)