PL/SQL Challenge 每周一题：2023-3-18 JSON对象访问表达式(21C)

newkid

最 先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：
http://www.kz486.com/forum.php?m ... eid&typeid=1808

原始出处：
https://devgym.oracle.com/

作者：Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开, 最 低版本要求：21C
注：本题给出答案时候要求给予简要说明才能得到奖品

我有一张表保存着Oracle公司办公室的地址：

create table qz_address (
   name     varchar2(20)
, address  json
);

insert into qz_address values (
   'Redwood Shores', json('{"Adr":["500 Oracle Parkway","Redwood Shores","CA","94065"]}')
);
insert into qz_address values (
   'World Headquartes', json('{"Adr":["2300 Oracle Way","Austin","TX","78741"]}')
);

commit;

column town format a30
column zip  format a10

开发者选择了以如下的JSON数组来代表地址：

第一个元素是街道和门牌号。
第二个元素是城镇名称。
第三个元素是州的简称。
第四个元素是邮政编码。

我想要列出所有办公室，包括它们的城镇名称和邮政编码。谓词我写了这个未完成的查询：

select
   a.name
##REPLACE##
from qz_address a
order by a.name;

哪些选项包含了列别名为TOWN 和 ZIP的表达式，可以用来取代##REPLACE##，使得查询执行不出错并且产生这个所需的输出：

NAME                 TOWN                           ZIP
-------------------- ------------------------------ ----------
Redwood Shores       Redwood Shores                 94065
World Headquartes    Austin                         78741

         
(A)
, a.address.Adr[1] as town
, a.address.Adr[3] as zip

(B)
, a.address.Adr[1].string() as town
, a.address.Adr[3].string() as zip

(C)
, json_value(a.address, '$.Adr[1]') as town
, json_value(a.address, '$.Adr[3]') as zip

(D)
, json_value(a.address, '$.Adr[1]' returning varchar2(30)) as town
, json_value(a.address, '$.Adr[3]' returning varchar2(10)) as zip

(E)
, json_query(a.address, '$.Adr[1]') as town
, json_query(a.address, '$.Adr[3]') as zip

(F)
, json_query(a.address, '$.Adr[1]' returning varchar2(30)) as town
, json_query(a.address, '$.Adr[3]' returning varchar2(10)) as zip

newkid

答案BCD
A:
因为ADDRESS列的数据类型是JSON, 我们通过圆点标记法返回的是一个数据类型为JSON的标量，这意味着带引号的字符串会出现在这个错误的输出中：

NAME                 TOWN                           ZIP
-------------------- ------------------------------ ----------
Redwood Shores       "Redwood Shores"               "94065"
World Headquartes    "Austin"                       "78741"

B:
我们可以修复前一选项，通过用string()方法来给我们实际的字符串内容, 也即数据类型为VARCHAR2的SQL标量值，这正是我们所要的。

C:
JSON_VALUE也是被设计成返回内容，而不是一个JSON的标量实例，所以这也能工作。

D:
前一选项的两个列的数据类型缺省为VARCHAR2(4000)，在我们的例子中输出是没问题的，因为我们已经用了SQL*Plus 的COLUMN 命令来将它们格式化为正确的大小，但是如果我们想要更小的实际数据类型，而不仅仅是输出，那么就可以用这个选项所示的RETURNING子句。

E:
JSON_QUERY是用来返回JSON的，虽然这看起来很像C选项，它实际上和A选项更像，并且给出了同样的错误输出。

F:
JSON_QUERY中的RETURNING仅仅是修改了返回的JSON标量的数据类型, 它仍然是一个JSON标量，包含了引号，并且给出了和A选项，E选项同样的错误输出。

lbx200309

学习了