SQL/JSON 函数 JSON_TABLE 创建 JSON 数据的关系视图。它将 JSON 数据评估的结果映射到关系行和列中。您可以使用 SQL 查询该函数返回的结果作为虚拟关系表。JSON_TABLE 的主要目的是为 JSON 数组中的每个对象创建一行关系数据，并将该对象中的 JSON 值输出为单独的 SQL 列值。
您必须仅在 SELECT 语句的 FROM 子句中指定 JSON_TABLE。该函数首先将路径表达式（称为 SQL/JSON 行路径表达式）应用于提供的 JSON 数据。与行路径表达式匹配的 JSON 值称为行源，因为它会生成一行关系数据。COLUMNS 子句评估行源，在行源中查找特定 JSON 值，并将这些 JSON 值作为一行关系数据的各个列中的 SQL 值返回。
先来看Oracle Blogs 文章中的例子。例子中的数据来自Facebook Graph API。
CREATE TABLE fb_tab (col JSON);
INSERT INTO fb_tab VALUES ( '
{
"data": [{
"from": {
"category": "Computers/technology",
"name": "Oracle"
},
"message": "How are Baxters Food Group and Elsevier taking their businesses to...",
"link": "http://onforb.es/1JOki7X",
"name": "Forbes: How The Cloud Answers Two Businesses Need For Speed ",
"description": "Cloud computing can support a companys speed and agility, ...",
"type": "link",
"created_time": "2015-05-12T16:26:12+0000",
"likes": {
"data": [{
"name": "Starup Haji"
},
{
"name": "Elaine Dala"
}
]
}
},
{
"from": {
"category": "Computers/technology",
"name": "Oracle"
},
"message": "Its important to have an IT approach that not only red...",
"link": "http://www.forbes.com/sites/oracle/2015/05/07/3-ways-you-can-avoid-sp...",
"name": "Forbes: 3 Ways You Can Avoid Spending Too Much On IT ",
"description": "Oracles suite of SaaS applications not only reduces costs but...",
"type": "link",
"created_time": "2015-05-11T19:23:11+0000",
"shares": {
"count": 5
},
"likes": {
"data": [{
"name": "Asal Alibiga"
},
{
"name": "Hasan Reni"
}
]
},
"comments": {
"data": [{
"from": {
"name": "Cesar Sanchez"
},
"message": "Thanks for this information",
"created_time": "2015-05-12T02:52:09+0000",
"like_count": 1
}]
}
}
]
}
' );
COMMIT;
此缩略数据包含 Oracle Facebook 源上的两个帖子。人们可以对帖子点赞和评论。每个帖子都是数组中的一项，可以使用路径表达式 $.data 进行访问。
由于 JSON _Table 会生成行，因此我们需要告诉它何时开始新行。这可以通过提供一个路径表达式来实现，该路径表达式选择我们想要投影为单独行的集合（数组）中的每个项目。在本例中，我们希望每个帖子都有一个新行。因此，路径表达式为：$.data[*].
SELECT jt.*
FROM fb_tab,
JSON_TABLE(col, '$.data[*]'
COLUMNS (
"Message" PATH '$.message'
)) "JT";
Message
----------------------------------------------------------------------------
How are Baxters Food Group and Elsevier taking their businesses to...
Its important to have an IT approach that not only red...
然后，对于通过此行路径表达式选择的每个项目，我们通过提供相对路径表达式（例如 $.message）来选择列值。
col message for a70
col type for a10
SELECT
jt.*
FROM
fb_tab,
JSON_TABLE ( col, '$.data[*]'
COLUMNS (
"Message" PATH '$.message',
"Type" VARCHAR2 ( 20 ) PATH '$.type',
"ShareCount" NUMBER PATH '$.shares.count' DEFAULT 0 ON ERROR
)
)
"JT";
Message Type ShareCount
---------------------------------------------------------------------- ---------- ----------
How are Baxters Food Group and Elsevier taking their businesses to... link 0
Its important to have an IT approach that not only red... link 5
从上例可知，JSON_TABLE包括3个参数，依次为：
- JSON文档
- 通过行路径表达式确定行
- 通过COLUMNS 子句确定列
另外，列ShareCount指定了DEFAULT 0 ON ERROR，是因为有一个数组元素没有shares域。
再来看一个更多列的例子：
col comments for a40
SELECT
jt.*
FROM
fb_tab,
JSON_TABLE ( col, '$.data[*]'
COLUMNS (
"Message" PATH '$.message',
"Type" VARCHAR2 ( 20 ) PATH '$.type',
"ShareCount" NUMBER PATH '$.shares.count' DEFAULT 0 ON ERROR,
"HasComments" NUMBER EXISTS PATH '$.comments',
"Comments" VARCHAR2 ( 4000 ) FORMAT JSON PATH '$.comments'
)
)
"JT";
Message Type ShareCount HasComments Comments
---------------------------------------------------------------------- ---------- ---------- ----------- ----------------------------------------
How are Baxters Food Group and Elsevier taking their businesses to... link 0 0
Its important to have an IT approach that not only red... link 5 1 {"data":[{"from":{"name":"Cesar Sanchez"
},"message":"Thanks for this information
","created_time":"2015-05-12T02:52:09+00
需要解释2点：
- HasComments中的EXISTS子句返回数组的0/1或字符串的 ‘true’/‘false’。由于类型指定为NUMBER，因此返回数值型。
- Comments列中的FORMAT JSON子句是必须有的。因为如果列是BLOB数据类型的列，则必须指定FORMAT JSON。如果为指定，也不会报错，但返回都是null。
再来看官方文档上几个比较复杂的例子。
第一个例子，使用了NESTED子句。可以将嵌套 JSON 对象或 JSON 数组中的 JSON 值与父对象或数组中的 JSON 值一起展平到一行中的单独列中。您可以递归使用此子句将多层嵌套对象或数组中的数据投影到一行中。
SELECT
jt.*
FROM
j_purchaseorder po,
JSON_TABLE ( po.po_document
COLUMNS (
"Special Instructions",
NESTED LineItems[*]
COLUMNS (
ItemNumber NUMBER,
Description PATH Part.Description
)
)
)
AS "JT"
;
Special Instructions ITEMNUMBER DESCRIPTION
------------------------------ ---------- --------------------------------------------------
Ground 1 Circuit 5
Ground 2 Eric Clapton: July 1986
Ground 3 Adventures of the Old West: The 49ers and the Cali
fornia Gold Rush
Courier 1 Genesi: La Creazione e il Diluvio
Courier 2 DVD Space Spectacular
Courier 3 Davy Crockett / Collectible Tin
Courier 4 An American in Paris
Hand Carry 1 Best of Musikladen Live: Ladies of Rock
Hand Carry 2 Dangerous Minds
...
在上例中，第一列来自JSON对象的第一层，后两列来自JSON对象中的第二层 – LineItems数组中的项。
下例和上例类似，不同的是指定了列名（尽管和JSON域一样），列类型。
SELECT jt.*
FROM j_purchaseorder po,
json_table(po.po_document,
'$'
COLUMNS (
"Special Instructions" VARCHAR2(4000)
PATH '$."Special Instructions"',
NESTED PATH '$.LineItems[*]'
COLUMNS (
ItemNumber NUMBER PATH '$.ItemNumber',
Description VARCHAR(4000) PATH '$.Part.Description'))
) AS "JT"
WHERE rownum < 10;
参考
- The new SQL/JSON Query operators (Part4: JSON_TABLE)
- JSON Developer’s Guide: SQL/JSON Function JSON_TABLE
- SQL Language Reference: JSON_TABLE
