Basics of Working with JSON in SQL Server
Basics of Working with JSON in SQL Server
The article gives an overview of working with JSON in SQL Server. Learn more about basic structure of a JSON document with examples.
Join the DZone community and get the full member experience.Join For Free
JSON – A Brief Background
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 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:
The document in Listing 1 was extracted from a regular SQL Server database table using the query from Listing 2. Listing 2 shows the feedback from SQL Server Management Studio upon the query execution: “9 Rows affected”. In essence, SQL Server converts each row in the source table to a JSON object. In each object, the column name is translated to the JSON name and the value for that column in that row is represented as the JSON value.
OPENJSON can be used to revert JSON formatted data to a relational format. Listing 3 shows an example of this using the first object in the sample JSON document referred to in Listing 1. The approach involves first defining a string variable @json and passing our JSON object as a parameter to this variable. We then pass the variable to the OPENJSON function in a SELECT statement. Running the query produces a result set with three columns: key, value, and type. JSON, unlike XML, has type definitions for each value in a document. In this case, we see Type 2 (numeric data) and Type 1 (string data) represented.
In Listing 4, we use the same approach with the entire JSON text including the square brackets  resulting in the output shown in Fig. 5. Notice the value in the Type column of this output (5) meaning the value we have in the field is a JSON object. Table 1 shows the list of JSON data types.
In order to represent the JSON data as the complete relational table, we started within Listing 2, we must specify the column names and data types we are converting to. We achieve this using the code in Listing 5. By comparing the output we get with the output when we query the HR.Employees table directly, we see that we are getting exactly the same data (See Fig. 6 and 7).
The ISJSON function performs a simple test to confirm whether a text document is represented in a valid JSON format. Listing 6 shows two ways of using this function to test a JSON document. By making one small change in the JSON document, we can get SQL Server to return a 0 (meaning: the document is NOT JSON) when we run this query. Just for fun, I will let you figure out the small change I made to the JSON object (see Fig. 8a and 8b).
It is worth mentioning, that using web sites such as https://jsonformatter.curiousconcept.com you can quickly validate JSON text or format prepared text as JSON.
In order to demonstrate the use of the functions JSON_VALUE, JSON_QUERY, and JSON_MODIFY, we create a table with a JSON column using the code in Listing 7. Note that the type for the column in question is a regular string data type NVACHAR(MAX). SQL Server does not have a special data type for JSON data in relational tables.
JSON_VALUE and JSON_QUERY appear similar but are different in the sense that while JSON_VALUE extracts scalar values from a JSON text, JSON_QUERY extracts objects or arrays. In other words, you are likely to get an error or a NULL if you try to extract a scalar value from a JSON text using JSON_QUERY. JSON_MODIFY allows you to change a specific value within JSON text that is stored within a column in a relational table. Listing 8 shows simple examples of using the JSON_* functions. While trying this out you will observe that the JSON path name is case sensitive. Microsoft documentation shows more examples of use cases for these functions.
SQL Server provides ample support for JSON thus helping to bridge the gap between SQL and No-SQL world. The functions described in this article as easy to learn and implement. There are more examples of their use as well as additional functions provided in Microsoft documentation. JSON and generally No-SQL is valuable knowledge that will help in the progression of the modern DBAs career.
Published at DZone with permission of Nathan Russel . See the original article here.
Opinions expressed by DZone contributors are their own.