Difference In JSON_VALUE Result In SQL Vs. PL/SQL (Doc ID 3043037.1)

Last updated on AUGUST 22, 2024

Applies to:

Symptoms

There are different query results on the use of JSON_VALUE in SQL vs. PL/SQL

A JSON object where a SQL query using JSON_VALUE returns the correct result, but the same use of JSON_VALUE in PL/SQL returns null.

Foro example

select json_value('{
 "store":{
 "book":[
 {
 "author":"Nigel Rees",
 "title":"Sayings of the Century",
 "price":8.95
 },
 {
 "author":"Evelyn Waugh",
 "title":"Sword of Honour",
 "price":12.99
 },
 {
 "author":"J. R. R. Tolkien",
 "title":"The Lord of the Rings",
 "price":22.99
 }
 ]
 }
 }', '$.store.book[*]?(@.price == 22.99).author') the_result from dual;

THE_RESULT
___________________
J. R. R. Tolkien

declare
 v_json varchar2(4000) := '{
 "store":{
 "book":[
 {
 "author":"Nigel Rees",
 "title":"Sayings of the Century",
 "price":8.95
 },
 {
 "author":"Evelyn Waugh",
 "title":"Sword of Honour",
 "price":12.99
 },
 {
 "author":"J. R. R. Tolkien",
 "title":"The Lord of the Rings",
 "price":22.99
 }
 ]
 }
 }';
 begin
 dbms_output.put_line( 'Value: ' || json_value(v_json, '$.store.book[*]?(@.price == 22.99).author') );
 end;
 /
Value:

PL/SQL procedure successfully completed.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.