ORA-31013 when reading a JSON_VALUE from a JSON_TABLE

Viewed 351

When I query a JSON_TABLE for a value using a JSON_VALUE expression (and not a COLUMN in the table expression) such as:

WITH SAMPLE_TABLE AS (
  SELECT '{"a":[{"b":"foo"},{"b":"bar"}]}' AS PAYLOAD FROM DUAL
)
SELECT JSON_VALUE(SUB, '$.b') 
FROM SAMPLE_TABLE, JSON_TABLE(
  PAYLOAD, 
  '$.a[*]' 
  COLUMNS (SUB CLOB FORMAT JSON PATH '$')
);

I get an error that I am using an invalid XPATH expression (ORA-31013: Invalid XPATH expression). The message alone confuses me but if I change the select to JSON_VALUE(TO_CHAR(SUB), '$.b'), the query works by showing two rows foo and bar what confuses me even more.

I do not have any such problems when using the XML-equivalent in Oracle where such selects just work. I am not using columns as this allows me to reuse a lot of stuff I already have for XML and beyond that, I am curious what is wrong here. I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0.

0 Answers
Related