I need to get the value of property x, but, aways return null
This doesn't work:
SELECT JSON_VALUE('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.c') AS value
FROM DUAL;
This works:
SELECT JSON_VALUE('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.a') AS value
FROM DUAL;
and returns 100
how can I retrieve or c property array using JSON_VALUE?
Your JSON has an array as the outer-most element so you need to dereference that first and then find the c key:
SELECT JSON_VALUE('[{a:100}, {b:200}, {c:300}]', '$[*].c') AS value FROM DUAL;
outputs:
| VALUE | | :---- | | 300 |
and since you have additional nested arrays and objects then:
SELECT JSON_VALUE('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$[*].c[0].xico') AS value FROM DUAL;
Outputs:
| VALUE | | :---- | | 1 |
db<>fiddle here
JSON_VALUE is a function that converts a scalar value in the JSON to a SQL scalar value. This is why the expression with $.a works - the associated value (100) is a scalar (number) value. On the other hand the path expression $.c selects a complex value, namely the array [{xico:1}]. Complex value can be returned with the JSON_QUERY function.
SELECT JSON_QUERY('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.c') AS value FROM DUAL;
[{"xico" : 1}]
The JSON_QUERY function also allows to select scalar values and return them wrapped in an array if you use the WITH WRAPPER clause.
SELECT JSON_QUERY('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.a' with wrapper) AS value FROM DUAL;
[100]
By the way: NULL is returned by default if an error is encountered when evaluating the path expression. You can change this default by supplying the ERROR ON ERROR clause:
SELECT JSON_VALUE('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.c' ERROR ON ERROR) FROM DUAL;
ORA-40456: JSON_VALUE evaluated to non-scalar value
or
SELECT JSON_QUERY('[{a:100}, {b:200}, {c:[{xico:1}]}]', '$.a' ERROR ON ERROR) FROM DUAL;
ORA-40480: result cannot be returned without array wrapper
