Overview
Ever consider how much more beneficial it would be for you to be able to simply arrange all your SQL result sets into JSON objects? Whether you have considered it or not, you can achieve desired set of results with SQL JSON Functions. In the article, we shall try to help ourselves understand its concept.
Fortunately, I recently had the chance to work with SQL JSON Functions which offers a fantastic alternative to traditional approach of getting data in JSON format from SQL tables. It enables you to combine relational and NoSQL ideas within a single database.
The JSON functions first added to SQL Server 2016 (13.x), which requires database compatibility level 130 or higher.
PreRequisites
We created a StudentManagement database with the 4 tables listed below, all of which include default data, to help illustrate the concept.
- [dbo].[Students] (Storing basic details)
- [dbo].[Students_Address] (Storing address details)
- [dbo].[Students_Emails] (Storing multiple emails)
- [dbo].[Students_Json] (Later to store JSON string of every student)
Click here, to copy the scripts used to create tables and databases with default values. 👈🏾
Facts
❌ If we wanted to read student data as JSON, the traditional approach would have looked something like this 👇
Output - {"FirstName":Edwin,"LastName":Hallwood,"Age":33}
✔️ Now that JSON functions have been introduced, it is remarkably simple to write the query below and obtain the same results.
💡 JSON Auto, Without_Array_Wrapper: What is it?
- To automatically format the JSON output according to the SELECT statement's structure, utilize the JSON Auto function.
- Here, the results are always return as an array of objects enclosed in [] brackets. Therefore, to have just object as the return type, all we must do is use the keyword Without_Array_Wrapper to eliminate the [] from the output.
Examples
1. How can the address information be made to appear as a child of the root object under the Address object?
To see the json output, click me 🙋
We have utilized JSON Path instead of JSON Auto in this instance, as the Auto will produce results based on the select statement. PATH mode allows you to have complete control over the output of the FOR JSON clause.
2. Let's look at an example where you want multiple EmailIds of the user to appear as a child of the root object under the Emails object?
To see the json output, click me 🙋
Note: If you want null values to show up in the JSON output, you must use the INCLUDE_NULL_VALUES option. If this option is not utilized, the output for query results excludes JSON properties for NULL values.
3. Let's say that you want the list of all students under the Root Students.
To see the json output, click me 🙋
💡Now, having seen the fundamentals, let us drill a little further and explore JSON functions and their broader implications.
Up till now, we have learned how to produce a JSON string. Now, we will use the Student_Json Table to dump the JSON for every student.
Currently, the For JSON clause returns, by default, a single JSON string containing array of objects (i.e., Rows). However, in order to record it against student_Id, we require a unique JSON string object for each student. We would create the query below to manage it.
When above query got executed, The JSON string for every student got dumped into the Students_Json database as shown in the figure below.👇
Now, let us play with the Students_Json Table. To get the desired outcomes, follow to the following examples that pertain to Student_Id = 1.
4. How to read the Address object from a JSON string?
JSON_QUERY extracts an object or an array from a JSON string.
JSON_VALUE extract a scalar value from a JSON string instead of
an object or an array.
5. How to read the FirstName from a JSON string?
Note the difference: Attempting to read the FirstName attribute with Json_Query would have returned null because it is not an array or object type. It was substituted with Json_Value. Try it out!
6. In a JSON string, change Edwin's first name to Thomas's.
To see the json output, click me 🙋
7. In a JSON string, changing the State to Chicago and adding a new Email address.
To see the json output, click me 🙋
8. How to Convert Json String to Sql RowSet?.
OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document
I hope you found this article enjoyable to read,If you would like, you can practice understanding the concept by going through each example one at a time. This will be very useful when working with JSON in SQL.
