Oracle JSON Query with hyphen-key

Question

I have an Oracle database in version 12.1.0.2.0 and JSON field that I want to query.

This query works:

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$."de"') from DUAL

This query returns the error ORA-40442.

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$."-"') from DUAL

Queries to the hyphen are no problem with MySQL and MSSQL.

I can't change the JSON-string.

How can I still query for the hyphen?

score 0 · Accepted Answer · answered Sep 26 '19 at 08:14

0

The solution is to use unicode representation:

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$."\u002d"') from DUAL

answered Sep 26 '19 at 08:14

Peter

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Both

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$."de"') from DUAL

and

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$."-"') from DUAL

works

unless JSON-path-expression containing non-alphanumeric character which is unquoted such as :

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$.-') from DUAL

or

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$.d-e') from DUAL

doesn't work and return the error ORA-40442

But

select JSON_VALUE('{"-": "hello", "de":"hallo"}','$.de') from DUAL

works without problem, even it's unquoted.

2 Answers2