All Blogs

Feeds

Archives
for this blog

My name is Peter Skoglund. I have twenty+ years experience in IT and I have work with SQL Server since version SQL Server 7.0. I work as a development DBA and spend all my days with development and database administration. My quote is "The first step to improvement is to have the gut to question".

JSON is syntax for storing and exchanging text information, similar to XML.

JSON is smaller than XML, and faster and easier to parse.

JSON is built on two structures:

• A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.

• An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

Why not XML?

XML is versatile and well known. You can create tags to match your own content and endlessly nest. So what isn’t there to like about it? Well, XML is too verbose and the amount of tags makes it difficult to read with the human eye and when compared to JSON, is JSON simpler and can represent the exact same data with fewer characters.

Ex XML

<?xml version="1.0" encoding="ISO-8859-1"?>

<bookstore>

<book category="COOKING">

<title lang="en">Everyday Italian</title>

<author>Giada De Laurentiis</author>

<year>2005</year>

<price>30.00</price>

</book>

<book category="CHILDREN">

<title lang="en">Harry Potter</title>

<author>J K. Rowling</author>

<year>2005</year>

<price>29.99</price>

</book>

</bookstore>

Ex JSON

{

"bookstore": {

"book": [

{

"-category": "COOKING",

"title": {

"-lang": "en",

"#text": "Everyday Italian"

},

"author": "Giada De Laurentiis",

"year": "2005",

"price": "30.00"

},

{

"-category": "CHILDREN",

"title": {

"-lang": "en",

"#text": "Harry Potter"

},

"author": "J K. Rowling",

"year": "2005",

"price": "29.99"

}

]

}

}

The agile storage and JSON

Let's assume we have a simple registration form with first name, last name and sex.In a typical SQL Server design we make a table with 3 columns for: first name, last name and sex and maybe identity generated id. We make a procedure to insert and get the data, last we put it all into production and it works fine.

CREATE TABLE [dbo].[RegisteredUsers](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](50) NOT NULL,

[LastName] [varchar](50) NOT NULL,

[Sex] [char](1) NOT NULL

)

Later we have a new requirement in the registration form, to store the age as well. In SQL Server we have to modify the schema and add an age column with that allows null, since we don’t know the current data’s requirement for the age column and we have to modify the procedures.

ALTER TABLE [dbo].[RegisteredUsers] ADD Age int NULL

What if we used a nosql database which stores data in key/value pair?The key is a unique id generated on saves from the registration form. A nosql database allows you to store the complete document as value to the given key and store it in JSON format, later you can retrieve the data and possibly directly pass to the javascript in UI.

{

"RegisteredUser": {

"-id": "a53e98e4-0197-4513-be6d-49836e406aaa",

"FirstName": "John",

"LastName": "Doe",

"Sex": "m"

}

}

and after the new requirement

{

"RegisteredUser": {

"-id": "3a768eea-cbda-4926-a82d-831cb89092aa",

"FirstName": "John",

"LastName": "Smith",

"Sex": "m"

"Age": "47"

}

}

Old data is just missing the Age key and new data gets it.

Store a JSON document in SQL Server?

Well, can’t we store a JSON document in SQL server in a key/value pair? Well of course we can.