JSON Support Is The Best New Developer Feature in SQL 2016 — Part 2: Creating JSON

Published on: 2017-01-31

This is the second article in my series about learning how to use SQL Server 2016’s new JSON functions. If you haven’t already, you can read Part 1 — Parsing JSON.


Last time we looked at SQL 2016’s new functions for parsing JSON data. Today, I want to explore the different options available for creating JSON data from a SQL result set.

The first option we have for creating JSON is by hardcoding a SQL string value. This is a terribly painful way to creating JSON and doesn’t use any of SQL 2016’s new functionality. However, for the sake of completeness I thought it would good to highlight the obvious:

Fortunately, SQL 2016 makes it much easier to generate JSON data from a query’s result set. First, let’s create some data to play around with:

The data above should look pretty familiar from Part 1 of this series. It’s only two rows and a handful of columns, but it should get the job done at showing how to use SQL’s two new JSON creating functions.

First up is the clause FOR JSON AUTO. This clause will take the results of a query and format them into very basic JSON. Not much magic here, but it sure beats having to hardcode (or build dynamic JSON strings) using the first method outlined above.

FOR JSON AUTO does offer some formatting flexibility though as shown in the examples: nesting JSON objects is possible through joining of tables.

Although FOR JSON AUTO is perfect for quick and dirty JSON string creation, SQL offers much more customization with FOR JSON PATH. TheFOR JSON PATH clause will take column aliases into consideration when building the JSON structure, making it unnecessary to have to join data in order to get a nested JSON schema.


And that’s it for creating JSON data in SQL Server 2016. Stay tuned over the next few weeks as we look at other SQL JSON functions that will help us modify data as well as a comparison of how SQL’s JSON functions perform against other languages JSON serialization/deserialization methods.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

JSON Support Is The Best New Developer Feature in SQL 2016 — Overview Part 1: Parsing JSON

Published on: 2017-01-24

As a developer my favorite new feature of SQL Server 2016 is JSON support.

I love JSON in SQL because I already love JSON everywhere outside of SQL: it uses much less space than XML for serializing data, it’s what most apps are now using for API communication, and when web developing I love that it is already valid JavaScript (no need to deserialize!).

I had this same type of excitement for XML in SQL Server, but after using it the excitement quickly turned into disappointment: having to constantly use the XML datatype was inconvenient (when most XML data I used was already stored in nvarchar(max) columns) and I never found the syntax of OPENXML() to be that intuitive.

Everything I’ve done with JSON in SQL Server 2016 so far has been great. I’ve already been storing persistent JSON in SQL, so being able to manipulate JSON within SQL is even better. In this series of posts I will go over the various functionalities of using JSON in SQL Server 2016:

Part 1 — Parsing JSON

What is JSON?

JavaScript Object Notation (JSON) “ is a lightweight data-interchange format.

My simple, mostly caveat-free* explanation is that it is a format for storing object data in JavaScript. It’s lightweight and easy to read, so it’s used in lots of applications that aren’t just JavaScript (although it’s especially easy to consume in JavaScript because it is JavaScript*).

*Caveats? See http://stackoverflow.com/a/383699

So what’s JSON look like? The JSON below represents the current inventory of cars in my garage. It shows I have two cars as well as some of their attributes:

Strict versus Lax mode

For any of the SQL JSON functions (OPENJSON(), JSON_VALUE(), JSON_QUERY(), andJSON_MODIFY()) you can specify whether invalid JSON paths will return NULL or an error. The default value is lax, which will return a NULL for non-existing JSON paths, whereas strict will return an error message.

The lax modifier is helpful when writing queries that check to see if values exist in a JSON object while the strict modifier works great for error checking and and validation.

ISJSON()

A simple function for verifying whether an inputted string is valid JSON. This is great to use in order to validate JSON formatting before running any of remaining functions in this post.

JSON_VALUE()

Extracts a scalar value from a JSON string. This function needs to be able to parse the value, so it will not parse out complex objects like arrays.

JSON_VALUE() is great for accessing operational data that might be using JSON to store multiple attributes for a single entry.

JSON_QUERY()

JSON_QUERY() is meant to work for all of the datatypes that JSON_VALUE() doesn’t know how to return: basically JSON_QUERY() returns JSON string representations of complex JSON objects like arrays.

It’s possible to use JSON_QUERY() along with JSON_VALUE() to essentially extract any type of data from JSON, whether it’s a simple or complex object datatype.

OPENJSON()

While JSON_VALUE() extracts singular scalar values and JSON_QUERY() extracts singular JSON strings, the OPENJSON() function extracts result sets from a JSON string. In addition to the extracted value, OPENJSON() outputs the order of JSON objects as well as their datatypes. OPENJSON() will also output string representations of JSON arrays instead of just displaying NULL, similar to JSON_QUERY().

The flexibility of OPENJSON() makes it possible to extract any values from JSON data, especially when combining OPENJSON() with JSON_VALUE(). The examples below show how to parse out a scalar value from complex JSON objects (like arrays). Note that using the WITH option gives us a lot more flexibility with how we can format our output result.


These JSON functions should help you parse any JSON data you encounter in SQL server (as long as it’s valid and stored as nvarchar). Stay tuned over the next few weeks as we look at other SQL JSON functions that will help us create data, modify data, and compare SQL’s JSON performance to other languages.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Present Like Nobody’s Watching

Published on: 2017-01-17

Thoughts On How Speaking At An Online-Only Conference Is Fun But Different

Presenting online — more like a one-on-one discussion than a presentation in front of hundreds of people

Last week I presented my session High Performance SSRS at the inaugural GroupBy Conference. It was an incredibly fun experience getting to present a topic I’m excited about as well seeing others do the same at such a high caliber.

The thing that makes GroupBy different from other technical conferences is that it’s free, it’s community driven, and it’s online-only. This makes it a very inclusive conference since barriers to attend are extremely low. The only significant barrier to watching the sessions live was a software restriction to the amount of live attendees. Fortunately, all of the sessions were made available on the GroupBy YouTube channel soon afterwards to allow anybody to watch the sessions at any time.

The 1,000 live attendee registration cap was quickly reached

Even though the conference is only half-over (more excellent presentations coming this Friday January 20 — go register!), my session is complete so I thought it would be fun to go over what made presenting at GroupBy different from an ordinary in-person conference.

Pre-Conference

There wasn’t too much of a difference preparing for GroupBy compared to other speaking engagements. I still had to create a presentation that captured the audience’s attention and told a good story. I also had a lot of rehearsing to do, especially since my presentation consisted of mostly live demos. In case you’ve never presented something with live demos, here’s a secret: something always goes wrong. This means a lot of my preparation involved thinking about what could go wrong and trying to prepare for it so I wouldn’t embarrass myself too badly in front of a live audience.

The main difference between this talk and others I’ve given is that I was entirely on my own in regards to audio and video setup.

Broadcasting from my kitchen on conference day

Having a both an audio (musician) and video (photographer) background, I probably went a little overboard with my setup. During my practice recording sessions, I kept finding little details that annoyed me and forced me to get to the setup that you see above. Ugly background? Move to the kitchen where there is a solid white wall. Bad lighting? Turn on all of the lights and bring in extra lamps. Laptop microphone sounds muffled? Pull out the condenser and boom stand.

Conference Day

Since I didn’t have to travel anywhere to speak, I just went about my normal day and went in to work. We booked a conference room (zing!) and had a watch party, ate some pizza, and learned about SQL Server 2016 built-in performance boosts, automation with PowerShell, SQL at StackOverflow, SQL Server 2016 hidden gems, what not to do with SQL, and Power BI. During the lunch break, I went home to get ready for my session.

My actual session went well and was strangely comfortable to present since I got to do it in my home, in my regular programming chair, 3 feet away from my regular programming fridge (need caffeine from somewhere right?).

The one daunting aspect of my presentation was that it was difficult getting a sense of how the audience was following along. Although Brent was great at laughing at my jokes, commenting, and moderating audience questions, it was hard for me to judge if I needed to speed up sections or slow down and explain in more detail. With a live audience, I can read body language and adjust. Online, it felt like nobody is watching.

Post-Conference

People were watching however, because soon after my presentation was over I got flooded with texts, emails, and comments about my session. Session ratings flowed in over the next few days and I was happy to get feedback indicating that my session was well received.

And although there were no speaker dinners or sponsored after parties, Renee and I made up for it by going to our favorite sushi bar to celebrate!

Presenting in the virtual world was definitely a very different experience for me but I loved it. Would I do it again? No doubt, and I hope you do too (abstract deadline is February 28 — get typing!).

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!