CODEWITHSUNDEEP
jsonsql-serverjson-query
user713813
user713813

Reputation: 785

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

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"
     }
]

Upvotes: 1

Views: 1714

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 1

Related Questions