Oracle 12c及以上版本json_table

json

适用于现代 C++ 的 JSON。

项目地址：https://gitcode.com/gh_mirrors/js/json

免费下载资源

数仓大山哥

5894人浏览 · 2019-11-08 11:13:12

数仓大山哥 · 2019-11-08 11:13:12 发布

运行环境：SQLPLUS, SERVEROUTPUT已打开
我有一张用户表，包含一个JSON列，里面是他们的社交媒体资料：

create table qz_users (
   id       integer primary key
, username varchar2(10)
, profiles varchar2(4000) check (profiles is json)
)
/

insert into qz_users values (68, 'Benjamin', '[
   { "site" : "twitter"  , "name" : "benjamin68" }
, { "site" : "facebook" , "name" : "benjyface"  }
]')
/
insert into qz_users values (96, 'Barbara', '[
   { "site" : "twitter"  , "name" : "barbiegirl" }
, { "site" : "instagram", "name" : "cutiebarb"  }
, { "site" : "pinterest", "name" : "barb1996"   }
]')
/
commit
/

我想要一个用户列表，连接到他们的社交媒体资料，有两个列包含着JSON列表中的site和name属性的值，如下所示：

        ID USERNAME   SITE       NAME
---------- ---------- ---------- ----------
        68 Benjamin   facebook   benjyface
        68 Benjamin   twitter    benjamin68
        96 Barbara    instagram  cutiebarb
        96 Barbara    pinterest  barb1996
        96 Barbara    twitter    barbiegirl

哪些选项执行不出错并且产生所示的输出?


(A)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles, '$[*]'
          columns (
             site varchar2(10) path '$.site'
           , name varchar2(10) path '$.name'
          )
       ) p
order by u.id, p.site
/

(B)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles, '$[*]'
          columns (
             site varchar2(10)
           , name varchar2(10)
          )
       ) p
order by u.id, p.site
/

(C)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles
          columns (
             site varchar2(10)
           , name varchar2(10)
          )
       ) p
order by u.id, p.site
/

(D)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles
          columns (
             site varchar2(10) path '$[*].site'
           , name varchar2(10) path '$[*].name'
          )
       ) p
order by u.id, p.site
/

(E)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles
          columns (
             site varchar2(10)
           , name varchar2(10)
          )
       ) p
order by u.id, p.site
/

(F)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles, '$[*]'
          columns (
             obj varchar2(4000) format json path '$'
          )
       ) o
     , json_table (
          o.obj, '$'
          columns (
             site varchar2(10) path '$.site'
           , name varchar2(10) path '$.name'
          )
       ) p
order by u.id, p.site
/

(G)
select u.id, u.username, p.site, p.name
  from qz_users u
     , json_table (
          u.profiles, '$[*]'
          columns (
             obj varchar2(4000) format json path '$'
          )
       ) o
     , json_table (
          o.obj
          columns (
             site varchar2(10)
           , name varchar2(10)
          )
       ) p
order by u.id, p.site
/

(H)
with p_obj as (
   select u.id, u.username, o.obj
     from qz_users u
        , json_table (
             u.profiles, '$[*]'
             columns (
                obj varchar2(4000) format json path '$'
             )
          ) o
    where rownum > 0
)
select p_obj.id, p_obj.username, p.site, p.name
  from p_obj
     , json_table (
          p_obj.obj, '$'
          columns (
             site varchar2(10) path '$.site'
           , name varchar2(10) path '$.name'
          )
       ) p
order by p_obj.id, p.site
/


(I)
with p_obj as (
   select u.id, u.username, o.obj
     from qz_users u
        , json_table (
             u.profiles, '$[*]'
             columns (
                obj varchar2(4000) format json path '$'
             )
          ) o
    where rownum > 0
)
select p_obj.id, p_obj.username, p.site, p.name
  from p_obj
     , json_table (
          p_obj.obj
          columns (
             site varchar2(10)
           , name varchar2(10)
          )
       ) p
order by p_obj.id, p.site
/

答案ABHI

A: JSON_TABLE的普通用法，指定了“记录”的路径以及每条记录中的列的路径。
B: 在18C版本中，我们可以跳过制定的列路径----于是路径就缺省为$.{列名}, 这意味着这个选项和前一选项是相同的。
C: 在18C版本中，你还可以跳过A选项中的“记录”路径，但是我们不能够使用[*]这种风格的访问路径表达式来得到数组中的每个对象。这会报错：
ORA-02000: missing COLUMNS keyword.
D: 如果我们完全跳过了[*], 那么JSON_TABLE的“记录”就变成了整个数组----因为我们没有指定“记录”路径，它缺省为'$'，那意味着我们得不到正确的行数，而列路径不起作用，导致了NULL值，总而言之我梦得到这个错误的输出：

      ID USERNAME SITE    NAME
---------- ---------- ---------- ----------
      68 Benjamin
      96 Barbara

E: 正如前一选项，JSON_TABLE的“记录”是整个数组。此处我们只是没有制定列路径，所以它们缺省为 $.site 和 $.name, 当这被应用于数组而不是数组中的对象，就导致了NULL值，所以我们得到了和前一选项相同的错误输出。

F:
原则上，这看起来像一个冗长但有效的与选项A相同的写法。但它不起作用，会报错：
ORA-40556: unsupported chaining of JSON_TABLE
随后的建议是使用NESTED PATH，但是如果我们尝试就会得到另一个错误：
ORA-40561: invalid path in JSON_TABLE nested path clause.
问题是，选择A做得很好，而这样做是没有意义的。

G: 原则上，这似乎比前一选项更有道理，因为创建O.OBJ列应该允许我们完全跳过第二个JSON_TABLE调用中的JSON路径说明。但不行的是，我们仍然会得到这个错误：
ORA-40556: unsupported chaining of JSON_TABLE.

H: 如果我们利用有个经典技巧，通过使用WHERE ROWNUM > 0把第一个JSON_TABLE的输出进行“物化”，我们就可以避免前两个选项的unsupported chaining错误。这是可行的，只是多了很多不必要的工作。
I: 利用这个技巧也允许我们跳过第二个JSON_TABLE中的路径表达式并且使得缺省值和前一选项完全一样。

其他DEMO处理展示：

--从JSONArray中取数据
SELECT jt.*
FROM JSON_TABLE('[
        {
            "device_type_id": 1,
            "amount": 120,
            "remarks": ""
        },
        {
            "device_type_id": 2,
            "amount": 122,
            "remarks": ""
        },
        {
            "device_type_id": 3,
            "amount": 123,
            "remarks": "11111111111"
        }
    ]','$'COLUMNS(NESTED PATH '$[*]' COLUMNS (device_type_id VARCHAR2(32) PATH '$.device_type_id',amount VARCHAR2(32) PATH '$.amount',remarks VARCHAR2(32) PATH '$.remarks')))
AS jt;
 
--从JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
    "detailed": [
        {
            "device_type_id": 1,
            "amount": 120,
            "remarks": ""
        },
        {
            "device_type_id": 2,
            "amount": 122,
            "remarks": ""
        }
    ]
}
','$'
COLUMNS
(NESTED PATH '$.detailed[*]' COLUMNS (device_type_id VARCHAR2(32) PATH '$.device_type_id',amount VARCHAR2(32) PATH '$.amount',remarks VARCHAR2(32) PATH '$.remarks')))
AS jt;
 
SELECT *
  FROM JSON_TABLE('{
                    "device_type_id": "1",
                    "amount": "120",
                    "remarks": ""
                }
',
                   '$' COLUMNS(outer_value_0 NUMBER PATH '$.device_type_id',
                           outer_value_1 NUMBER PATH '$.amount'));
 
--从三层嵌套的JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
    "certificate": "14531209693428a799591c0248bb95c3",
    "rows": [
        {
            "odo_id": "0",
            "odo_no": "ZC-FY-20170217001",
            "stamp": "2017-02-24",
            "order_no": "ZC-DD-20170210001",
            "partners_id": "213",
            "shipping_address": "深圳市福田区科技园南区T2-B栋601",
            "contacts": "李魁",
            "tel": "13510141822",
            "self_mention": "0",
            "detailed": [
                {
                    "device_type_id": "1",
                    "amount": "121",
                    "remarks": ""
                },{
                    "device_type_id": "2",
                    "amount": "122",
                    "remarks": ""
                }
            ]
        },{
            "odo_id": "0",
            "odo_no": "ZC-FY-20170217002",
            "stamp": "2017-02-24",
            "order_no": "ZC-DD-20170210001",
            "partners_id": "213",
            "shipping_address": "深圳市福田区科技园南区T2-B栋601",
            "contacts": "李魁",
            "tel": "13510141822",
            "self_mention": "0",
            "detailed": [
                {
                    "device_type_id": "3",
                    "amount": "123",
                    "remarks": ""
                },{
                    "device_type_id": "4",
                    "amount": "124",
                    "remarks": ""
                }
            ]
        }
    ]
}
', '$'
COLUMNS
  (requestor VARCHAR2(32) PATH '$.certificate',NESTED PATH '$.rows[*]' COLUMNS (odo_no VARCHAR2(32) PATH '$.odo_no',NESTED PATH '$.detailed[*]' COLUMNS (phone_type VARCHAR2(32) PATH '$.device_type_id', phone_num VARCHAR2(20) PATH '$.amount'))))
AS jt;

原文链接：http://www.itpub.net/thread-2109001-1-1.html （newkid）

https://blog.csdn.net/e_wsq/article/details/60150077

【侵权删】

GitHub 加速计划 / js / json

下载

适用于现代 C++ 的 JSON。

最近提交(Master分支：2 个月前 )

960b763e 5 个月前

8c391e04 8 个月前

GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

[转载]在Windows环境下安装GNU Radio

转自：在Windows环境下安装GNURadio_恐弱智_新浪博客GNU Radio是用Python开发的，大部分开源的工程能够在Linux环境下运行良好，而Windows下却运行的很勉强，而且安装配置都很复杂。GNU Radio算是个例外了，不光提供了Windows的二进制安装，还有比较详细的说明。我是Python小白，所以折腾了好久才弄好，特意记录下来，免得以后再装还折腾。GNU Radio的

GitCode 开源社区

centOS 8 使用dnf安装Docker

DNF是什么？CentOS 8使用YUM软件包管理器版本v4.0.4。现在，该版本使用DNF(已删除YUM)。DNF是软件包管理器。它会在Linux发行版上安装，执行更新并删除软件包。使用DNF安装Docker跳过具有损坏依赖性的程序包一个有效的解决方案是使您的CentOS 8系统使用以下--nobest命令安装最符合条件的版本：sudo dnf install docker...

GitCode 开源社区

定时同步数据库表(mysql+linux+crontab)

sync.sh里面的参数需要改变，ip/username/password/database/tablesync.sh#!/bin/sh# Please change the IP and password of the data source db.# Then change the table name.filename=/home/nington/db/$(date +%Y-%m