Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Basics of Working with JSON in SQL Server

Basics of Working with JSON in SQL Server

In this blog post, we will show you how to use json in SQL server.

JSON – A Brief Background

JSON is an acronym for JavaScript Object Notation, that became popular a little over seventeen years ago. JSON is essentially a data format, it was popularized by Douglas Crockford, a well-known programmer with an interesting history who was also involved in the development of JavaScript. JSON has nearly replaced XML as a cross-platform data exchange format. It is reported to be lightweight and easier to manipulate compared to XML. In AWS CloudFormation, templates, which are actually JSON (or YAML) formatted documents, are used to describe AWS resources when automating deployments.

JSON is also used extensively in NoSQL databases such as the increasingly popular MongoDB. Virtually all the Social Media giants expose APIs that are based on JSON. I am sure you begin to get the idea of how widespread its applications have become. JSON was standardized in 2013 and the latest version of the standard (ECMA-404: The JSON Data Interchange Syntax) was released in 2017.

SQL Server introduced support for JSON in SQL Server 2016.

JSON Format

JSON documents are represented as a series of JSON objects that contain name-value pairs. JSON objects can increase in complexity as we introduce components which are not just single values but arrays in themselves. The following shows the format of a JSON document based on the EMCA-404 standard:

-- Listing 1: Sample JSON Document
[
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}
]

1

Fig. 1 Basic Structure of a JSON Document

The document in Listing 1 was extracted from a regular SQL Server database table using the query from Listing 2. Listing 2 shows the feedback from SQL Server Management Studio upon the query execution: “9 Rows affected”. In essence, SQL Server converts each row in the source table to a JSON object. In each object, the column name is translated to the JSON name and the value for that column in that row is represented as the JSON value.

-- Listing 2: Using the FOR JSON Clause
USE TSQLV4
GO
SELECT * FROM HR.Employees 
FOR JSON AUTO;

USE TSQLV4
GO
SELECT * FROM HR.Employees 
FOR JSON PATH;

2

Fig. 2 Returning a ResultSet in JSON Format

SQL Server JSON Functions

In the previous section, we used the FOR JSON clause which is designed to format query results as JSON. SQL Server in its turn provides the following functions to manipulate JSON formats inside SQL Server:
3

OPENJSON

OPENJSON can be used to revert JSON formatted data to a relational format. Listing 3 shows an example of this using the first object in the sample JSON document referred to in Listing 1. The approach involves first defining a string variable @json and passing our JSON object as a parameter to this variable. We then pass the variable to the OPENJSON function in a SELECT statement. Running the query produces a result set with three columns: key, value, and type. JSON, unlike XML, has type definitions for each value in a document. In this case, we see Type 2 (numeric data) and Type 1 (string data) represented.

-- Listing 3 Using OPENJSON 
DECLARE @json NVARCHAR(4000) = N'{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';

SELECT * FROM OPENJSON (@json);

4

Fig. 3 ResultSet from Listing 3

In Listing 4, we use the same approach with the entire JSON text including the square brackets [] resulting in the output shown in Fig. 5. Notice the value in the Type column of this output (5) meaning the value we have in the field is a JSON object. Table 1 shows the list of JSON data types.
5

-- Listing 4 Using OPENJSON
DECLARE @json NVARCHAR(4000) = N'
[{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}]';

SELECT * FROM OPENJSON (@json);

6

Fig. 5 ResultSet from Listing 4

In order to represent the JSON data as the complete relational table, we started within Listing 2, we must specify the column names and data types we are converting to. We achieve this using the code in Listing 5. By comparing the output we get with the output when we query the HR.Employees table directly, we see that we are getting exactly the same data (See Fig. 6 and 7).

-- Listing 5 Using OPENJSON
DECLARE @json NVARCHAR(4000) = N'
[{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}]';

SELECT * FROM OPENJSON (@json) 
WITH (

empid int '$.empid',
lastname varchar(100) '$.lastname',
firstname varchar(100) '$.firstname',
title varchar(100) '$.title',
titleofcourtesy varchar(100) '$.titleofcourtesy',
birthdate date '$.birthdate',
hiredate date '$.hiredate',
address varchar(300) '$.address',
city varchar(100) '$.city',
postalcode int '$.postalcode',
country char(2) '$.country',
phone varchar(20) '$.phone',
mgrid int '$.mgrid')
;

7

Fig. 6 ResultSet from Listing 5

8

Fig. 7 ResultSet from Querying HR.Employees

ISJSON

The ISJSON function performs a simple test to confirm whether a text document is represented in a valid JSON format. Listing 6 shows two ways of using this function to test a JSON document. By making one small change in the JSON document, we can get SQL Server to return a 0 (meaning: the document is NOT JSON) when we run this query. Just for fun, I will let you figure out the small change I made to the JSON object (see Fig. 8a and 8b).

-- Listing 6 Using ISJSON
-- Basic Check for JSON Format

DECLARE @json NVARCHAR(4000) = N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';

SELECT ISJSON (@json);


-- Check Using WITH Clause and CASE Expression

DECLARE @json NVARCHAR(4000) = N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';

WITH JSONTEST as (SELECT ISJSON (@json) [IS JSON ?] )
SELECT 
CASE [IS JSON ?] 
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END AS [IS JSON ?]
FROM JSONTEST;

9

It is worth mentioning, that using web sites such as https://jsonformatter.curiousconcept.com you can quickly validate JSON text or format prepared text as JSON.

JSON_* Functions

In order to demonstrate the use of the functions JSON_VALUE, JSON_QUERY, and JSON_MODIFY, we create a table with a JSON column using the code in Listing 7. Note that the type for the column in question is a regular string data type NVACHAR(MAX). SQL Server does not have a special data type for JSON data in relational tables.

-- Listing 7: Creating a Relational Table with JSON Data

USE [TSQLV4]
GO

/****** Object:  Table [HR].[Employees_JSON]    Script Date: 1/13/2020 10:03:52 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [HR].[Employees_JSON](
    [empid] [int] IDENTITY(1,1) NOT NULL,
    [lastname] [nvarchar](20) NOT NULL,
    [firstname] [nvarchar](10) NOT NULL,
    [title] [nvarchar](30) NOT NULL,
    [titleofcourtesy] [nvarchar](25) NOT NULL,
    [birthdate] [date] NOT NULL,
    [hiredate] [date] NOT NULL,
    [address] [nvarchar](60) NOT NULL,
    [city] [nvarchar](15) NOT NULL,
    [region] [nvarchar](15) NULL,
    [postalcode] [nvarchar](10) NULL,
    [country] [nvarchar](15) NOT NULL,
    [phone] [nvarchar](24) NOT NULL,
    [mgrid] [int] NULL,
    [jsondata] [nvarchar](max) NULL,
 CONSTRAINT [PK_Employees_JSON] PRIMARY KEY CLUSTERED 
(
    [empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Insert one row in the HR.Employees_JSON Table

INSERT INTO [HR].[Employees_JSON](
lastname
, firstname
, title
, titleofcourtesy
, birthdate
, hiredate
, address
, city
, region
, postalcode
, country
, phone
, mgrid
, jsondata)

SELECT TOP 1 
lastname
, firstname
, title
, titleofcourtesy
, birthdate
, hiredate
, address
, city
, region
, postalcode
, country
, phone
, mgrid
,N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}'
FROM HR.Employees;

JSON_VALUE and JSON_QUERY appear similar but are different in the sense that while JSON_VALUE extracts scalar values from a JSON text, JSON_QUERY extracts objects or arrays. In other words, you are likely to get an error or a NULL if you try to extract a scalar value from a JSON text using JSON_QUERY. JSON_MODIFY allows you to change a specific value within JSON text that is stored within a column in a relational table. Listing 8 shows simple examples of using the JSON_* functions. While trying this out you will observe that the JSON path name is case sensitive. Microsoft documentation shows more examples of use cases for these functions.

-- Listing 8: JSON_* Samples 
-- Display A Single Columns Using JSON_VALUE

SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title
FROM HR.Employees_JSON

-- Display Two Columns Using JSON_VALUE
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_VALUE(jsondata,'$.titleofcourtesy') AS TitleofCourtesy
FROM HR.Employees_JSON

-- Attempt QUerying a JSON Value Using JSON_QUERY (NULL Returned)
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_QUERY(jsondata,'$.titleofcourtesy') AS TitleofCourtesy
FROM HR.Employees_JSON

-- Query a JSON Object Using JSON_QUERY
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_QUERY(jsondata,'$') AS TitleofCourtesy
FROM HR.Employees_JSON

-- Attempt Querying a JSON Object Using JSON_VALUE (NULL Returned)
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$') AS Title
,JSON_QUERY(jsondata,'$') AS TitleofCourtesy
FROM HR.Employees_JSON;

-- Update a value in JSON text using JSON_MODIFY
DECLARE @jsondata varchar(max)
SELECT @jsondata= jsondata FROM HR.Employees_JSON;
SET @jsondata = JSON_MODIFY(@jsondata,'$.title','GCEO')
PRINT @jsondata

UPDATE HR.Employees_JSON 
SET jsondata=@jsondata;

SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title
FROM HR.Employees_JSON;

Conclusion

SQL Server provides ample support for JSON thus helping to bridge the gap between SQL and No-SQL world. The functions described in this article as easy to learn and implement. There are more examples of their use as well as additional functions provided in Microsoft documentation. JSON and generally No-SQL is valuable knowledge that will help in the progression of the modern DBAs career.

Check out my guide on how to work with JSON in SQL Server for more information

Programming Web Development
0 1 1
Share on

Read previous post:

5 Reasons Why Cloud Security Is Important For All Businesses!

Read next post:

Git Branching Naming Convention - Best Practices

Hiteshjethva

38 posts | 4 followers

You may also like

Comments

Hiteshjethva

38 posts | 4 followers

Related Products

More Posts by Hiteshjethva

See All