Oracle 12c及以上版本json_table
运行环境：SQLPLUS, SERVEROUTPUT已打开我有一张用户表，包含一个JSON列，里面是他们的社交媒体资料：create table qz_users (idinteger primary key, username varchar2(10), profiles varchar2(4000) check (profiles is json))/...
运行环境：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
【侵权删】
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
3
0- 0
已为社区贡献2条内容
所有评论(0)