Basic JSON Queries–#SQLNewBlogger

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:

2020-11-21 14_58_17-SQLQuery3.sql - ARISTOTLE_SQL2017.Compare2 (ARISTOTLE_Steve (58))_ - Microsoft S

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.

2020-11-24 14_49_16-

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

4 Responses to Basic JSON Queries–#SQLNewBlogger

  1. Pingback: JSON Basics with SQL Server – Curated SQL

  2. Jeff Moden says:

    Heh… it’s almost as bad as XML when it comes to selecting data. 😀

    That, notwithstanding, really nice post, Steve. I’m not a JSON user (yet) and so I’m curious… what does the “$” mean and is it ever replaced with something more specific?

    Like

  3. Pingback: No Scalars with JSON_QUERY–#SQLNewBlogger | Voice of the DBA

  4. Pingback: No Scalars with JSON_QUERY–#SQLNewBlogger from Blog Posts - SQLServerCentral - Actu Tech

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.