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


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:

     = N'
  {  "player": {
              "name" : "Sarah",
              "position" : "setter, DS"

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.


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.