Basics of Working with JSON in SQL Server

Image for post
Image for post

JSON — A Brief Background

JSON Format

-- 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
}
]
Basic Structure of a JSON Document
Basic Structure of a JSON Document
Fig. 1 Basic Structure of a JSON Document
-- 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;
Returning a ResultSet in JSON Format
Returning a ResultSet in JSON Format
Fig. 2 Returning a ResultSet in JSON Format

SQL Server JSON Functions

Image for post
Image for post

OPENJSON

-- 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);
ResultSet from Listing 3
ResultSet from Listing 3
Fig. 3 ResultSet from Listing 3
Image for post
Image for post
-- 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);
ResultSet from Listing 4
ResultSet from Listing 4
Fig. 5 ResultSet from Listing 4
-- 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')
;
Image for post
Image for post
Fig. 6 ResultSet from Listing 5
Image for post
Image for post
Fig. 7 ResultSet from Querying HR.Employees

ISJSON

-- 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 ExpressionDECLARE @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;
Image for post
Image for post
Fig. 8a IS JSON. Fig. 8b IS NOT JSON.

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store