No Scalars with JSON_QUERY–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I started to dig into JSON queries recently, and as I continued to experiment with JSON, this struck me as strange. Why is there a NULL in the result?

2020-12-04 14_43_02-SQLQuery3.sql - ARISTOTLE_SQL2017.Compare2 (ARISTOTLE_Steve (58))_ - Microsoft S

The path looks right. This appears to be somewhere I ought to get a result back. As I looked up the JSON_QUERY documentation, and it says I get an object or array back. I’d somewhat expect that position, while containing a single value, could be seen as an object of

{“setter”}

The fact that I need to know I have a single value here seems like poor design. If the document changes, perhaps someone might enter this:

DECLARE @json NVARCHAR(1000)
     = N'
  {  "player": {
              "name" : "Sarah",
              "position" : "setter, DS"
             },
    "team":"varsity"
  }
';

In this case, a JSON_VALUE would fail, while a JSON_QUERY wouldn’t work in the first example above. This means that I need to modify my code based on documents.

I don’t like this, but I need to know this, so if you work with JSON, make sure you know how the functions work.

SQLNewBlogger

While writing the previous post, I changed one of the function calls and got the NULL. I had to fix things for the other post, but I kept the query and then spent about 10 minutes writing this one to show a little thought into the language.

You can easily take something you are confused about, made a mistake doing, or wonder about and write your own post.

About way0utwest

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

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.