title: Is it possible to use wildcards and ranges with JSON_VALUE?
body: <p>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 <a href="http://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56668" rel="nofollow">JSON_VALUE</a>.</p>
  
  <p>The <a href="http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15dba-2372211.html" rel="nofollow">example</a> I am using is from the January/February 2015 issue of Oracle Magazine and here is the SQL I am stuck on.</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>The article mentions that </p>
  
  <blockquote>
    <p>...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...</p>
  </blockquote>
  
  <p>So I am expecting that the second and third lines of the query return all the CheckNumber values (101 and 102).</p>
  
  <p>I am using Oracle Database 12.1.0.2.0 and here is the table and insert script in case of need:</p>
  
  <pre><code>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
                     }
                 ]
     }'
   )
   /
  </code></pre>
  
  <p>I know I must be missing something obvious!</p>