0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zeir
  • 249
  • 1
  • 4
  • 13

2 Answers2

1

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
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"]

Zeir
  • 249
  • 1
  • 4
  • 13