JSON documents in SQL Server and Azure SQL Database

These contents working in SQL Server 2016 and later and Azure SQL Database.

1. JSON documents in SQL Server

Use nvarchar(max) data type to store JSON documents up to 2 GB in size in a column of a SQL Server table. If your JSON is less then 8 KB is recommended to use nvarchar(4000) for performance reasons.

To see if a string contains a valid JSON you can use the function ISJSON(). ISJSON(column) It returns 1 if the column contains valid JSON, otherwise returns 0.

-- Extract only the records containing a valid JSON in the Post_Json column
SELECT *
FROM dbo.Posts
WHERE IS_JSON(Post_Json) > 0

You can use constraints with ISJSON() function to enforce proper formatting of your JSON column CHECK(ISJSON(column)=1).

2. Query JSON with T-SQL

To query a JSON column, we could use JSON_VALUE() and JSON_QUERY() functions, with the JSON column and the JSON path (we start it with $ and navigate the JSON structure using dots).

JSON_VALUE() to retrieve a single value (number or text) in a JSON column. JSON_VALUE(jsoncolumn, '$.employee.birthyear') It returns a single text value of type nvarchar(4000).

JSON_QUERY() to retrieve a JSON object or array from a JSON column. JSON_QUERY(jsoncolumn, '$.employee.address') It returns a JSON fragment of type nvarchar(max).

We can use JSON_VALUE and JSON_QUERY both in the select and in the where part of a query.

-- The Column Post_Json in the table dbo.Posts contains a JSON
SELECT JSON_VALUE(Post_Json, '$.Post.Title') AS Title
    ,JSON_QUERY(Post_Json, '$.Post.Author') AS Author  -- used JSON_QUERY because the result is a JSON object not only a value
    ,JSON_VALUE(Post_Json, '$.Post.Comments[0].Comment.Text') AS First_Comment  -- first comment in the path
FROM dbo.Posts
WHERE JSON_VALUE(Post_Json, '$.Post.Title') = 'AWS in Python with Boto3'

3. Format Query Results as JSON with T-SQL

Now I want to retrieve a JSON from traditional columns that contain structured data. We can use FOR JSON PATH or FOR JSON AUTO

-- With FOR JSON PATH we have full control over the format
SELECT *
FROM dbo.Table1
FOR JSON PATH, ROOT ('Id')

-- With FOR JSON AUTO SQL Server choose the best structure based on the select
SELECT *
FROM dbo.Table1
FOR JSON AUTO

4. Update a JSON with T-SQL

To modify a JSON column with T-SQL, we use the function JSON_MODIFY() with the JSON column, the JSON path and the new value. JSON_MODIFY(jsoncolumn, '$.employee.birthyear', '1983')

UPDATE dbo.Posts
SET Post_Json = JSON_MODIFY(Post_Json, '$.Post.Title', 'Basic Scala')
WHERE JSON_VALUE(Post_Json, '$.Post.Id') = '61'

5. JSON Function Summary in SQL Server

Recap:

ISJSON(): see if a string contains a valid JSON
JSON_VALUE(): extract a single value from a JSON string
JSON_QUERY(): exrtact an object or an array from a JSON string
JSON_MODIFY(): modify a JSON column

6. Importing and parsing JSON

You can import JSON files from disk using OPENROWSET.

DECLARE @JSONfile varchar(max);

SELECT @JSONfile = BulkColumn   -- BulkColumn is the return value from OPENROWSET
FROM OPENROWSET (BULK 'C:\Documents\sqlfiles\json\post.json', SINGLE_CLOB) AS j;  -- SINGLE_CLOB tells OPENROWSET to interpret the document as a single varchar(max) column

PRINT @JSONfile;

You can import JSON objects directly from a string using OPENJSON and parse it as a table.

DECLARE @JSONfile varchar(max);

SET @JSONfile = N'[
{
  "firstName": "John",
  "lastName": "Doe",
  "age": 38
},
{
  "firstName": "Jane",
  "lastName": "Doe",
  "age": 25
}';

SELECT *
FROM OPENJSON (@JSONfile) 
WITH (
   firstName varchar(50),
   lastname varchar(50),
   age int
   ) AS People

7. Handling missing properties with strict mode

STRICT will raise an error if the property is missing. JSON_VALUE(jsoncolumn, 'strict$.employee.birthyear')

LAX is the opposite, not raise an error if the property is missing and it is the default. JSON_VALUE(jsoncolumn, 'lax$.employee.birthyear') is the same of JSON_VALUE(jsoncolumn, '$.employee.birthyear')