Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Recently I saw Jason Horner do a presentation on JSON at a user group meeting. I’ve lightly looked at JSON in some detail, and I decided to experiment with this.
Basic Querying of a Document
A JSON document is text that contains key-value pairs, with colons used to separate them, and grouped with curly braces. Arrays are supported with brackets, values separated by commas, and everything that is text is quoted with double quotes.
There are a few other rules, but that’s the basic structure. Things can next, and in SQL Server, we store the data a character data. So let’s create a document:
DECLARE @json NVARCHAR(1000) = N'
{
"player": {
"name" : "Sarah",
"position" : "setter"
}
"team" : "varsity"
}
'
This is a basic document, with two key values (player and team) and one set of additional keys (name and position) inside the first key.
I can query this with the code:
SELECT JSON_VALUE(@json, '$.player.name') AS PlayerName;
This returns the scalar value from the document. In this case, I get “Sarah”, as shown here:
I need to get the path correct here for the value. Note that I start with a dot (.) as the root and then traverse the tree. A few other examples are shown in the image.
These show the paths to get to data in the document.
In a future post, I’ll look in more detail how this works.
SQLNewBlogger
After watching the presentation, I decided to do a little research and experiment. I spent about 10 minutes playing with JSON and querying it, and then another 10 writing this post.
This is a great example of picking up the beginnings of a new skill, and the start of a blog series that shows how I can work with this data.
Voice of the DBA 