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