JSON function JSON_VALUE returns invalid json for a string

Mansa 20 Reputation points
2024-07-29T17:37:09.2+00:00

Given a json object:

DECLARE @jsonObject NVARCHAR(MAX) = '
{
    "name": "John Doe",
    "age": 30,
    "address": {
        "city": "New York",
        "state": "NY"
    },
    "tags": ["sql", "json", "tsql"]
}';
select JSON_VALUE(@jsonObject, '$.name')

select isjson(JSON_VALUE(@jsonObject, '$.name'), SCALAR)
select isjson(JSON_VALUE(@jsonObject, '$.name'), VALUE)

select JSON_VALUE(@jsonObject, '$.name'), the result is 'John Doe' but not '"John Doe"' , the stripping of quotes invalidates the json string literal '"John Doe"' and now it is just a string literal 'John Doe' instead of a json string literal '"John Doe"'

This looks like a bug.

Is there a way to extract a valid json string literal from a json object?

Here is what I am trying to do: I am trying to implement a functionality that given a valid json string (can be a scalar or an object/array, and a given a path, extract the value.)

The query I am working with right now is

select 
coalesce(
	json_query( case when isjson(@jsonObject, SCALAR) = 1 then null else j end, 	'$.address.city')), 
	json_value( case when isjson(@jsonObject, SCALAR) = 1 then null else j end, '$.address.city' )) 

However now that the string values are invalid json literals, I will need to do some checking and add quotes using isjson but that makes it very complicated and isjson() could also return true for scalars null and value

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 71,101 Reputation points
    2024-07-29T20:15:15.49+00:00

    sqlserver json support does not include json literals. all json string must start with a "[" or "{", that is the json string must represent an array or object.

    > select isJson('"hi"') as notJson, isjson('{"a":"hi"}') as Json
    notJson	Json
    0	    1
    
    > select * from openjson('"hi"') 
    Msg 13609, Level 16, State 4, Line 2
    JSON text is not properly formatted. Unexpected character '"' is found at position 0.
    
    > select * from openjson('{"a":"hi"}') 
    key	value	type
    a	hi	    1
    
    > select * from openjson('["hi"]') 
    key	value	type
    0	hi	    1
    

    notice for arrays the key is the index value.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.