是否可以对JSON_VALUE使用通配符和范围? [英] Is it possible to use wildcards and ranges with JSON_VALUE?

问题描述

我目前正在研究一个使用Oracle 12c的JSON功能的示例，我正在努力了解如何在

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.

示例我是使用来自《 Oracle Magazine》 2015年1月/2月号，这是我坚持使用的SQL.

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 

文章提到

...显示全部 值，Mark使用CheckDetails [*]. (他可以使用特定数字 拉特定的项目，例如CheckDetails [0,2]拉第一个 和第三项...

...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...

因此，我希望查询的第二行和第三行返回所有CheckNumber值(101和102).

So I am expecting that the second and third lines of the query return all the CheckNumber values (101 and 102).

我正在使用Oracle Database 12.1.0.2.0，如果需要，这是表和插入脚本:

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!

推荐答案

JSON_VALUE旨在返回标量.要返回JSON对象或关系数据，必须使用JSON_QUERY或JSON_TABLE.

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

这篇关于是否可以对JSON_VALUE使用通配符和范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！