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

Question

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?

I also tried adding double quotes - Works for me - check your JSON truly contains a scalar value at that location — Caius Jard
– Caius Jard, Commented Oct 26, 2020 at 13:55
@CaiusJard yes noticed it thx to ur comment and edited right after — Zeir
– Zeir, Commented Oct 26, 2020 at 15:14

Caius Jard · Accepted Answer · 2020-10-26 15:19:58Z

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

Zeir · Accepted Answer · 2020-10-26 15:35:07Z

0

Replacing JSON_VALUE with JSON_QUERY fixed my problem.

So below code works fine

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

Returns ["55.06"]

answered Oct 26, 2020 at 15:35

Zeir

3091 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related