sqljsonjson-value

How to fix SQL server JSON_VALUE path has '-' error

I'm trying to get values of fields in sql that is in JSON type. unfortunately path has '-' so that I get an error when I try to get the value.

field->** **{"Con":["AA"],"X-Location-Latitude":["55.06"]}

When I try to get json value with below query

SELECT JSON_VALUE(field, '$.X-Location-Latitude') 
FROM table

I get this error

Json path is not properly formatted unexpected character '-' is found

I also tried adding double quotes as

SELECT JSON_VALUE(field, '$."X-Location-Latitude"') 
FROM table

and this returned NULL.

Does anyone have a solution?

Solution

  • So you originally posted:

    field-> {"Con":"[AA]","X-Location-Latitude":"[55]"}

    "[55]" is a single string of length 4; '$."X-Location-Latitude"' is correct for that single value.

    I see now that you've changed it to:

    {"Con":["AA"],"X-Location-Latitude":["55.06"]}

    ["55.06"] is a completely different thing (and not because of the .06); it's an array of strings, with one element "55.06". This makes all the difference to JSON_VALUE

    JSON_VALUE cannot be used with a path that selects an array; you'll have to nominate a single value in the array, for example:

    SELECT JSON_VALUE(field, '$."X-Location-Latitude"[0]') FROM table
                                                 ^^^
                                          first array element

    JSON_VALUE must be used with scalar (single value) properties, not collections/arrays(things enclosed in []) or complex objects (things enclosed in {})

    The fine manual has a nice table about what happens when you try select various things with JSON_VALUE, in lax and strict mode