Is it possible to use wildcards and ranges with JSON_VALUE?

json, oracle, oracle12c

Solution

`JSON_VALUE` is meant to return scalars. To return JSON objects or relational data you must use `JSON_QUERY` or `JSON_TABLE`.

select json_query(t.trans_msg, '$.CheckDetails[0].CheckNumber' with wrapper)   a,
       json_query(t.trans_msg, '$.CheckDetails[*].CheckNumber' with wrapper)   b,
       json_query(t.trans_msg, '$.CheckDetails[0,1].CheckNumber' with wrapper) c
from transactions t
where t.id = 1;

A       B           C
-----   ---------   ---------
[101]   [101,102]   [101,102]


select id, details
from transactions
cross join json_table
(
    trans_msg, '$.CheckDetails[*]'
    columns
    (
        details path '$.CheckNumber'
    )
);

ID   DETAILS
--   -------
1    101
1    102

Problem

I am currently working through an example of using the JSON features of Oracle 12c and I am struggling to understand how to use wildcards and ranges with JSON_VALUE. The example I am using is from the January/February 2015 issue of Oracle Magazine and here is the SQL I am stuck on. ``` select json_value(t.trans_msg, '$.CheckDetails[0].CheckNumber'), -- works returns 101 json_value(t.trans_msg, '$.CheckDetails[*].CheckNumber'), -- null returned json_value(t.trans_msg, '$.CheckDetails[0,1].CheckNumber') -- null returned from transactions t where t.id = 1 ``` The article mentions that ...To show all the values, Mark uses CheckDetails[*]. (He can use specific numbers to pull specific items—for example, CheckDetails[0,2] to pull the first and third items... So I am expecting that the second and third lines of the query return all the CheckNumber values (101 and 102). I am using Oracle Database 12.1.0.2.0 and here is the table and insert script in case of need: ``` create table transactions( id number not null primary key, trans_msg clob, constraint check_json check (trans_msg is json) ) / insert into transactions (id, trans_msg ) VALUES (1, '{ "TransId" : 1, "TransDate" : "01-JAN-2015", "TransTime" : "11:05:00", "TransType" : "Deposit", "AccountNumber" : 123, "AccountName" : "Smith, John", "TransAmount" : 100.00, "Location" : "ATM", "CashierId" : null, "ATMDetails" : { "ATMId" : 301, "ATMLocation" : "123 Some St, Danbury CT 06810" }, "WebDetails" : { "URL" : null }, "Source" : "Check", "CheckDetails" : [ { "CheckNumber" : 101, "CheckAmount" : 50.00, "AcmeBankFlag" : true, "Endorsed" : true }, { "CheckNumber" : 102, "CheckAmount" : 50.00, "AcmeBankFlag" : false, "Endorsed" : true } ] }' ) / ``` I know I must be missing something obvious!

Original source