JSON is a simple data format used for data interchange. The structure of JSON content follows the syntax structure for JavaScript. This data format is built on two structures:
- A collection of name/value pairs
- An ordered list of values
Example 29.1 shows an example of a JSON document.
Example 29.1
(“info”: (“who”: “Fred” .”where”: “Microsoft”,
“friends”:[{“name”: “Lili”, “rank”:5},{“name”: “Hank”, “rank”: 7}]}}
Example 29.1 shows a JSON string called info, which describes a single person, Fred, his affiliation, Microsoft, and his friends, Lili and Hank. Generally, a JSON string contains either an array of values or an object, which is an array of name/value pairs. An array is surrounded by a pair of square brackets and contains a comma-separated list of values. An object is surrounded by a pair of curly brackets and contains a comma-separated list of name/value pairs. A name/ value pair consists of a field name (in double quotes), followed by a colon (:), followed by the field value (in double quotes).
From Example 29.1 we have the following strings:
- “name”: “Hank” This is a name/value pair.
- {“name”: “Hank”, “rank”: 7} This is an object.
- [{“name”:”Lili”, “rank”: 5} ,{“name”: “Hank”, “rank”: 7}]
This is an array of objects.
A value in an array or object can be a number, a string, the NULL value, another array, or another string. A string must be written in double quotes, an array must be written in a pair of square brackets, and an object must be written in a pair of curly brackets.
NOTE To put actual double quotes inside strings, use the backslash character to escape the double quotes.
Why Support JSON in SQL Server?
The Database Engine has supported XML for a long time. Therefore, it is important to address why Microsoft added support for JSON as of SQL Server 2016. There are several reasons why JSON is supported in SQL Server:
- Integration of semistructured data As you already know, relational tables contain structured data. The advantage of JSON is that it can contain both structured and semistructured data. By supporting the storage of JSON objects, the Database Engine extends its capabilities and integrates structured and semistructured data together.
- Reduced administrative costs through use of data stores When JSON objects are stored individually and are used for separate programs, each program has to administer its own data. With JSON support through the Database Engine, the DBMS administers all data. The same is true for security and transaction management, because the DBMS takes over the management of security and transaction processing, meaning that this functionality does not need to be implemented in users’ programs.
- Increased developer productivity Using a DBMS to support JSON increases productivity because the DBMS takes over a lot of tasks that otherwise must be implemented in programs.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.