First what is JSON?
JSON: JavaScript Object Notation.
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.
CREATE TABLE [dbo].[RegisteredUsers](
[Id] [uniqueidentifier] NOT NULL,
[Json] [nvarchar](max) NOT NULL
)
Ex data:
Id Json
3a768eea-cbda-4926-a82d-831cb89092aa {"Id":"3a768eea-cbda-4926-a82d-831cb89092aa""Firstname":"John","Lastname":"Doe","Sex":"m"}
What if we wanted query the JSON data?
But what if we wanted to query the data in SQL Server? Let’s say we wanted to list male registered user? Well, we have to do a like search, ouch….
Nosql database like Mongodb, CouchBase, Riak, RavenDB, etc. all ready have good support for JSON and the important querying and parsing of JSON structures. SQL Server have no native support!
Some clever people have made custom JSON support for SQL Server, like http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/to bridge the gap but even that it’s not enough.
Plea for native support
Our only option is to partition and plea Microsoft for better JSON support and here is the way todo it:
Let’s hope we get it before SQL Server 2015
NEWS UPDATE (SQL SERVER 2016)
http://www.sqlservercentral.com/blogs/sql-development-wizard/2016/12/20/sql-server-2016-has-reduced-the-nosql-json-advantage-over-sql-server/
NEWS UPDATE (SQL SERVER 2016)
http://www.sqlservercentral.com/blogs/sql-development-wizard/2016/12/20/sql-server-2016-has-reduced-the-nosql-json-advantage-over-sql-server/
