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.