ITPUB论坛-中国最专业的IT技术社区

   
 注册
热搜:
查看: 610|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2018-3-20 JSON对象访问表达式

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
跳转到指定楼层
1#
发表于 2018-3-23 05:43 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

以往旧题索引:
/forum.php?m ... eid&typeid=1808

原始出处:


作者: Kim Berg Hansen

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

注:本题执行环境为12.2或更高

我们的角色扮演游戏系统的数据包括玩家角色资料,自定了玩家角色的技能级别。这些数据从游戏系统中以JSON格式发送给我们,存放在这张临时表:

create table qz_profile_stage (
   stage_json  clob  check(stage_json is json)
)
/
insert into qz_profile_stage values ('
{
   "ProfileName" : "danthedaring"
}
')
/
insert into qz_profile_stage values ('
{
   "ProfileName" : "conan"
, "Skills" : [ "Armwrestling", "Darts", "Swordthrowing", "Forgery" ]
}
')
/
insert into qz_profile_stage values ('
{
   "ProfileName" : "blackadder"
, "Skills" : [ "Spellcasting", "Basketweaving" ]
}
')
/
commit
/

为了快速查看这些资料,我们想要一个名字列表已经他们的前三个技能。

哪些选项包含了一个查询,能够执行不出错并且返回这个所需的输出:

NAME            FIRST_SKILL     SECOND_SKILL    THIRD_SKILL
--------------- --------------- --------------- ---------------
blackadder      Spellcasting    Basketweaving
conan           Armwrestling    Darts           Swordthrowing
danthedaring
(A)
select ps.stage_json.profilename as name
     , ps.stage_json.skills[0] as first_skill
     , ps.stage_json.skills[1] as second_skill
     , ps.stage_json.skills[2] as third_skill
  from qz_profile_stage ps
order by ps.stage_json.profilename
/

(B)
select ps.stage_json.profilename as name
     , ps.stage_json.skills[1] as first_skill
     , ps.stage_json.skills[2] as second_skill
     , ps.stage_json.skills[3] as third_skill
  from qz_profile_stage ps
order by ps.stage_json.profilename
/

(C)
select ps.stage_json.ProfileName as name
     , ps.stage_json.Skills[0] as first_skill
     , ps.stage_json.Skills[1] as second_skill
     , ps.stage_json.Skills[2] as third_skill
  from qz_profile_stage ps
order by ps.stage_json.ProfileName
/

(D)
select ps.stage_json.ProfileName as name
     , ps.stage_json.Skills[1] as first_skill
     , ps.stage_json.Skills[2] as second_skill
     , ps.stage_json.Skills[3] as third_skill
  from qz_profile_stage ps
order by ps.stage_json.ProfileName
/

(E)
select ps.stage_json."ProfileName" as name
     , ps.stage_json."Skills"[0] as first_skill
     , ps.stage_json."Skills"[1] as second_skill
     , ps.stage_json."Skills"[2] as third_skill
  from qz_profile_stage ps
order by ps.stage_json."ProfileName"
/

(F)
select ps.stage_json."ProfileName" as name
     , ps.stage_json."Skills"[1] as first_skill
     , ps.stage_json."Skills"[2] as second_skill
     , ps.stage_json."Skills"[3] as third_skill
  from qz_profile_stage ps
order by ps.stage_json."ProfileName"
/

(G)
select name, first_skill, second_skill, third_skill
  from qz_profile_stage
     , json_table(
          stage_json
        , '$'
          columns (
             name         varchar2(15) path '$.ProfileName'
           , first_skill  varchar2(15) path '$.Skills[0]'
           , second_skill varchar2(15) path '$.Skills[1]'
           , third_skill  varchar2(15) path '$.Skills[2]'
          )
       )
order by name
/

(H)
select name, first_skill, second_skill, third_skill
  from qz_profile_stage
     , json_table(
          stage_json
        , '$'
          columns (
             name         varchar2(15) path '$.ProfileName'
           , first_skill  varchar2(15) path '$.Skills[1]'
           , second_skill varchar2(15) path '$.Skills[2]'
           , third_skill  varchar2(15) path '$.Skills[3]'
          )
       )
order by name
/


(I)
select name, first_skill, second_skill, third_skill
  from (
   select name, row_num, skill
     from qz_profile_stage
        , json_table(
             stage_json
           , '$'
             columns (
                name    varchar2(15) path '$.ProfileName'
              , nested  path  '$.Skills
  • '
                       columns (
                          row_num for ordinality
                        , skill   varchar2(15) path '$'
                       )
                 )
              )
      )
    pivot (
       max(skill) as skill
       for (row_num) in (
          0 as first
        , 1 as second
        , 2 as third
       )
    )
    order by name
    /

    (J)
    select name, first_skill, second_skill, third_skill
      from (
       select name, row_num, skill
         from qz_profile_stage
            , json_table(
                 stage_json
               , '$'
                 columns (
                    name    varchar2(15) path '$.ProfileName'
                  , nested  path  '$.Skills
  • '
                       columns (
                          row_num for ordinality
                        , skill   varchar2(15) path '$'
                       )
                 )
              )
      )
    pivot (
       max(skill) as skill
       for (row_num) in (
          1 as first
        , 2 as second
        , 3 as third
       )
    )
    order by name
    /
  • 论坛徽章:
    135
    秀才
日期:2015-11-23 09:48:22秀才
日期:2016-02-18 09:23:46秀才
日期:2016-02-18 09:24:302016猴年福章
日期:2016-02-18 09:31:30秀才
日期:2016-02-18 09:39:10秀才
日期:2016-02-18 10:06:46秀才
日期:2016-02-18 10:08:02秀才
日期:2016-02-18 10:08:14秀才
日期:2016-03-01 09:57:08天蝎座
日期:2016-03-18 14:23:56
    2#
    发表于 2018-3-23 09:34 | 只看该作者
    感觉是C,E,G,I

    使用道具 举报

    回复
    论坛徽章:
    0
    3#
    发表于 2018-3-23 16:59 | 只看该作者
    应该是选H吧

    使用道具 举报

    回复
    论坛徽章:
    486
    秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
    4#
     楼主| 发表于 2018-3-24 04:50 | 只看该作者
    答案CEGJ, 本期无人得奖。

    A: JSON 对象访问表达式是大小写敏感的,而普通SQL表达式则不是。这里的表达式全部在JSON中都找不到键,所以我们得到这个错误输出:

    NAME            FIRST_SKILL     SECOND_SKILL    THIRD_SKILL
    --------------- --------------- --------------- ---------------




    (四行全部为空)

    B: 对JSON数组索引值的修改没有作用,因为所有的小写属性名仍然在JSON中匹配不到任何键,我们得到和前一选项一样的错误输出。
    C: 这次我们的属性键名称和JSON里面的骆驼型(指每个单词首字母大写其余小写)键名匹配,所以我们能够得到正确输出。JSON数组从索引0开始,所以前三个技能的数字为0,1和2.
    D: 和选项B不同的是我们确实得到了非空输出,因为属性键名称大小写是正确的。但是此处我们错误地认为JSON数组中第一个值的索引数字为1——这是不对的,它是从0开始的,正如前一选项所示。这个选项会给出这个错误结果:

    NAME            FIRST_SKILL     SECOND_SKILL    THIRD_SKILL
    --------------- --------------- --------------- ---------------
    blackadder      Basketweaving
    conan           Darts           Swordthrowing   Forgery
    danthedaring

    E: 我们可以将属性键名放在双引号中,正如我们通常在SQL中的做法,这样可以把列名或列别名变成大小写敏感的。然而,既然它是一个JSON对象访问表达式,这不会引起什么变化,它即使不带双引号也是大小写敏感的。这和选项C相同,给出相同的正确输出。

    F: 正如选项E和选项C相同(双引号对大小写敏感并不是必要的),本选项和选项D也是相同的,给了我们相同的错误输出,因为我们错误第将[1]用作第一个技能。

    G: 如果不走JSON对象访问表达式的捷径,我们也可以像这样明确使用JSON_TABLE并且指定列名和路径。如果有可能,优化器会将选项C和E的点记法改写为像当前选项这样的JSON_TABLE取值。但是手工书写JSON_TABLE允许我们明确设置列的数据类型,也可以支持更为复杂的JSON路径表达式。

    H: 不管是不是用JSON_TABLE,JSON数组中第一个元素是数字0的规则依然有效。所以这个选项给出和选项D和F一样的错误输出。
    I: 此处我们用了NESTED PATH 来为每种技能生成一行,对于这些技能我们在ROW_NUM给出了数字并且带了FOR ORDINALITY。然后我们在PIVOT子句中用它来为每个名字得出一行,用skills作列。

    此处的问题在于,FOR ORDINALITY不是用0来作为JSON数组中的第一个元素——它用的是1。所以这个选项给出这个错误的输出:

    NAME            FIRST_SKILL     SECOND_SKILL    THIRD_SKILL
    --------------- --------------- --------------- ---------------
    blackadder                      Spellcasting    Basketweaving
    conan                           Armwrestling    Darts
    danthedaring

    J: 于是我们通过在PIVOT中使用1,2和3而不是0,1和2,修正了前一选项的错误,得到了正确的输出。

    使用道具 举报

    回复
    论坛徽章:
    0
    5#
    发表于 2018-3-26 09:53 | 只看该作者
    I 跟 J里的 nested  path  '$.Skills  显示有些问题,   应该是nested  path  '$.Skills[*]'

    使用道具 举报

    回复

    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
      ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
      | | |
    CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
     北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
      
    快速回复 返回顶部 返回列表