Hi - I'm getting an "ORA-40442: JSON path expression syntax error" error when trying to extract the value for a JSON document key name starting with the @ symbol (@odata.count). I've tried this successfully on test data where I removed the leading @ (odata.count). Thanks, Mike (12c Enterprise Edition Release 12.1.0.2.0)
The test data below has one example with the @ symbol and the other without:
DROP TABLE JSON_ATSIGN_TEST;
CREATE TABLE JSON_ATSIGN_TEST
(TEST_TYPE VARCHAR2(30),BBDW_JSON_TEST_RESPONSE CLOB)
LOB (BBDW_JSON_TEST_RESPONSE) STORE AS SECUREFILE (
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 32768
NOCACHE
LOGGING)
TABLESPACE USERS;
ALTER TABLE JSON_ATSIGN_TEST ADD (
CONSTRAINT ENSURE_JSON_DOC
CHECK (BBDW_JSON_TEST_RESPONSE is json)
ENABLE VALIDATE);
INSERT INTO JSON_ATSIGN_TEST
(TEST_TYPE,BBDW_JSON_TEST_RESPONSE)
VALUES
('WITH@','{"@odata.context":"https://something.com/Dim_Addons","@odata.count":1}');
--
INSERT INTO JSON_ATSIGN_TEST
(TEST_TYPE,BBDW_JSON_TEST_RESPONSE)
VALUES
('WITHOUT@','{"odata.context":"https://something.com/Dim_Addons","odata.count":1}');
--works
SELECT JSON_VALUE(BBDW_JSON_TEST_RESPONSE, '$."odata.count"') odata_count
from JSON_ATSIGN_TEST
WHERE TEST_TYPE='WITHOUT@';
ODATA_COUNT
--------------------------------------------------------------------------------
1
1 row selected.
--does not work
SELECT JSON_VALUE(BBDW_JSON_TEST_RESPONSE, '$."@odata.count"') odata_count
from JSON_ATSIGN_TEST
WHERE TEST_TYPE='WITH@';
Error at line 1
ORA-40442: JSON path expression syntax error