[SOLVED] Query for JSON value with unexpected character in path

Query for JSON value with unexpected character in path

I'm working with JSON values in a database for the first time. I want to use JSON_VALUE or JSON_QUERY to return a value or section of the JSON, but whoever designed this went and used '-' in the keys, which is an illegal value. Research suggests I need to use FOR JSON to escape it, but I can't figure out how.

Attempt at the query:

select 
    xt.ID,
    JSON_QUERY(xt.JSON_DB_VALUE, '$.CusomterQuery.Details.cust-names') as JSON_WITH_NAMES,
    JSON_VALUE(xt.JSON_DB_VALUE, '$.CusomterQuery.Details.cust-names.first-name') as FIRST_NAME
from EXAMPLE_TABLE xt

Error received:

JSON path is not properly formatted. Unexpected character '-' is found at position xx.

Thanks

Solution

thanks to Zhorov's quick comment, this is the very simple solution - add quotation marks around the parts of the path with the illegal character.

select 
    xt.ID,
    JSON_QUERY(xt.JSON_DB_VALUE, '$.CusomterQuery.Details."cust-names"') as JSON_WITH_NAMES,
    JSON_VALUE(xt.JSON_DB_VALUE, '$.CusomterQuery.Details."cust-names"."first-name"') as FIRST_NAME
from EXAMPLE_TABLE xt