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
– Charlieface, Commented Mar 25, 2021 at 1:50
@Charlieface I just tried that and still got NULL for the Guid. — user713813
– user713813, Commented Mar 25, 2021 at 1:59
"JSON_value([json], '$[1].Guid') ... returns null" -- No it does not. — sticky bit
– sticky bit, Commented Mar 25, 2021 at 2:18

Zhorov · Accepted Answer · 2021-03-25 07:19:20Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related