How do you use JSON_QUERY with null json array inside of json object?

Question

SELECT JSON_query([json], '$') from mytable

Returns fine the contents of [json] field

SELECT JSON_query([json], '$.Guid') from mytable

Returns null

SELECT JSON_query([json], '$.Guid[1]') from mytable

Returns null

I've also now tried:

SELECT JSON_query([json], '$[1].Guid') 
SELECT JSON_query([json], '$[2].Guid') 
SELECT JSON_query([json], '$[3].Guid') 
SELECT JSON_query([json], '$[4].Guid')

and they all return null

So I'm stuck as to figuring out how create the path to get to the info. Maybe SQL Server json_query can't handle the null as the first array?

Below is the string that is stored inside of the [json] field in the database.

[
  null,
  {
    "Round": 1,
    "Guid": "15f4fe9d-403c-4820-8e35-8a8c8d78c33b",
    "Team": "2",
    "PlayerNumber": "78"
  },
  {
    "Round": 1,
    "Guid": "8e91596b-cc33-4ce7-bfc0-ac3d1dc5eb67",
    "Team": "2",
    "PlayerNumber": "54"
      },
  {
    "Round": 1,
    "Guid": "f53cd74b-ed5f-47b3-aab5-2f3790f3cd34",
    "Team": "1",
    "PlayerNumber": "23"
    
  },
  {
    "Round": 1,
    "Guid": "30297678-f2cf-4b95-a789-a25947a4d4e6",
    "Team": "1",
    "PlayerNumber": "11"
     }
]

`JSON_query([json], '$[1].Guid')` replace 1 with whichever array index you want (zero-based). I suspect you want `JSON_VALUE` not `JSON_QUERY` though — Charlieface, Mar 25 '21 at 01:50
@Charlieface I just tried that and still got NULL for the Guid. — user713813, Mar 25 '21 at 01:59
@Charlieface is it possible that the first array being null is causing the problem? I seem to remember trying to concatenate strings in sql and if one of those strings contained a null then the whole string would be null. maybe something similar? — user713813, Mar 25 '21 at 02:02
@user713813: All of them? As JSON array? Or in rows? Or just the one from the second element? Or the first? Be precise, best thing would be a table that looks like the table you expect the query to output. And [edit] the question to add such crucial additional info. Don't hide it in comments. — sticky bit, Mar 25 '21 at 02:03
And as a side note: Your JSON schema seems fixed. If that assumption is true, you should consider redesigning your database schema get rid of the JSON and store the information the relational way. — sticky bit, Mar 25 '21 at 02:07
1. There is only one array here, with a bunch of items in it 2. Concatenation with null irrelevant 3. You *still* haven't told us exactly what you are trying to get. Is it one `Guid` key or all of them? — Charlieface, Mar 25 '21 at 02:08
@charlieface I'd like to get to all of the Guid's. But I'll take getting to just one to see it work ;) — user713813, Mar 25 '21 at 02:10
You need `JSON_VALUE` for one value, for multiple in rows you will need `OPENJSON` — Charlieface, Mar 25 '21 at 02:10
@stick-bit it seems fixed but it's not. There are hundreds of pieces of different data. I just simplified it here for the example. — user713813, Mar 25 '21 at 02:11
@charlieface okay, but JSON_value([json], '$[1].Guid') or JSON_value([json], '$.Guid') returns null — user713813, Mar 25 '21 at 02:13
"JSON_value([json], '$[1].Guid') ... returns null" -- No it [does not](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5b3372920a24cceddd961043901fb15a). — sticky bit, Mar 25 '21 at 02:18
@sticky-bit I tried your exact example on my box and it worked. but when the [json] variable comes directly from the table it returns NULL. very strange. — user713813, Mar 25 '21 at 02:46
I suggest you [edit] and post some real sample data for us to work with (`CREATE TABLE` and `INSERT`s preferably), as well as expected output — Charlieface, Mar 25 '21 at 02:53
@stick-bit thanks for your help. when I pasted my full json into your example sql threw the error "incorrect syntax near t" and it turned out there is an apostrophe (the word doesn't) for some of the data. example https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7b3b9bfbbb4cc606cd7e92fe037aeb0e to see the error. but apostrophe's have been around a long time inside of json's. why can't it handle it? — user713813, Mar 25 '21 at 03:20
@user713813: Nothing strange about that. In string literals single quotes need to be escaped by doubling them. That's got nothing to do with JSON, all string literals need to follow that. — sticky bit, Mar 25 '21 at 12:16

score 1 · Accepted Answer · answered Mar 25 '21 at 07:19

You need to follow the comments below your question. I'll just summarize them:

Probably the most appropriate approach in your case is to use OPENJSON() with explicit schema (the WITH clause).
JSON_QUERY() extracts a JSON object or a JSON array from a JSON string and returns NULL. If the path points to a scalar JSON value, the function returns NULL in lax mode and an error in strictmode. The stored JSON doesn't have a $.Guid key, so NULL is the actual result from the SELECT JSON_query([json], '$.Guid') FROM mytable statement.

The following statements provide a working solution to your problem:

Table:

SELECT *
INTO Data
FROM (VALUES
   (N'[
  null,
  {
    "Round": 1,
    "Guid": "15f4fe9d-403c-4820-8e35-8a8c8d78c33b",
    "Team": "2",
    "PlayerNumber": "78",
    "TheProblem": "doesn''t"
  },
  {
    "Round": 1,
    "Guid": "8e91596b-cc33-4ce7-bfc0-ac3d1dc5eb67",
    "Team": "2",
    "PlayerNumber": "54"
      },
  {
    "Round": 1,
    "Guid": "f53cd74b-ed5f-47b3-aab5-2f3790f3cd34",
    "Team": "1",
    "PlayerNumber": "23"
    
  },
  {
    "Round": 1,
    "Guid": "30297678-f2cf-4b95-a789-a25947a4d4e6",
    "Team": "1",
    "PlayerNumber": "11"
     }
]')
) v (Json)

Statements:

SELECT j.Guid
FROM Data d
OUTER APPLY OPENJSON(d.Json) WITH (
   Guid uniqueidentifier '$.Guid',
   Round int '$.Round',
   Team nvarchar(1) '$.Team',
   PlayerNumber nvarchar(2) '$.PlayerNumber'
) j

SELECT JSON_VALUE(j.[value], '$.Guid')
FROM Data d
OUTER APPLY OPENJSON(d.Json) j

Result:

Guid
------------------------------------

15f4fe9d-403c-4820-8e35-8a8c8d78c33b
8e91596b-cc33-4ce7-bfc0-ac3d1dc5eb67
f53cd74b-ed5f-47b3-aab5-2f3790f3cd34
30297678-f2cf-4b95-a789-a25947a4d4e6

How do you use JSON_QUERY with null json array inside of json object?

1 Answers1