Basics of Working with JSON in SQL Server

{coding}Sight
8 min readApr 24, 2020

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)…
{coding}Sight

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