Oracle使用Json_table函数解析Json对象

Oracle Database 12c中的有一个JSON函数是JSON_TABLE，返回值不是标量值而是结果集。JSON_TABLE将JSON文档(部分)转换为关系表。

1 JSON_TABLE例子 首先，我将创建一个表t1，其中的一列为JSON类型，并向表中插入一个JSON文档:

CREATE TABLE t1(json_col JSON); INSERT INTO t1 VALUES ('{ "people": [ { "name": "John Smith", "address": "780 Mission St, San Francisco, CA 94103" },

{ "name": "Sally Brown", "address": "75 37th Ave S, St Cloud, MN 94103" }, { "name": "John Johnson", "address": "1262 Roosevelt Trail, Raymond, ME 04071" } ] }');

我可以使用以下SQL查询转换为关系表的文件:

SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS(NAME VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address')) people;

因为JSON_TABLE返回一个结果集，所以可以在FROM子句中使用它。

JSON_TABLE接受以下参数:

1.JSON数据源:这个表达式可以引用FROM列表中前面表中的列。在本例中，json_col指的是包含JSON文档的列。

(注意，按照SQL标准的规定，前面的表和引用该表的JSON_TABLE之间有一个隐式的横向连接(lateral join)。换句话说，对于前面的表的每一行，将“调用”JSON_TABLE函数。)

2.应该转换为表的JSON数组的路径。在本例中，它是people数组的对象。

3.那些表中的列，其列名、类型和路径应该在JSON对象中可以找到值。 我们还需要给返回的表一个名称，这里我们称它为people。

以上查询将返回以下结果: NAME ADDRESS John Smith 780 Mission St, San Francisco, CA 94103 Sally Brown 75 37th Ave S, St Cloud, MN 9410 John Johnson 1262 Roosevelt Trail, Raymond, ME 04071

 

2 对JSON文档的关系操作 当我们使用JSON_TABLE将JSON文档转换为表时，我们可以使用“SQL工具集”并对数据执行诸如选择和聚合之类的关系操作。

例如，我们可以使用这个查询来选择只叫John的人:

SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS(NAME VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address')) people;

WHERE people.name LIKE 'John%'; NAME ADDRESS John Smith 780 Mission St, San Francisco, CA 94103 John Johnson 1262 Roosevelt Trail, Raymond, ME 04071

如上所示，我们可以通过JSON_TABLE对JSON文档执行关系操作。

 

3 嵌套的JSON数组 JSON_TABLE还可以处理嵌套JSON数组。

给定以下JSON数组与家族对象，有数组与子对象:

[ { "father": "John", "mother": "Mary", "children": [ { "age": 12, "name": "Eric" }, { "age": 10, "name": "Beth" } ], "marriage_date": "2003-12-05" }, { "father": "Paul", "mother": "Laura", "children": [ { "age": 9, "name": "Sarah" }, { "age": 3, "name": "Noah" }, { "age": 1, "name": "Peter" } ] } ]

我们想要将这个文档转换为每个子元素对应一行的表:

ID FATHER MARRIED CHILD_ID CHILD AGE

1 John 1 1 Eric 12

1 John 1 2 Beth 10

2 Paul 0 1 Sarah 9

2 Paul 0 2 Noah 3

2 Paul 0 3 Peter 1

JSON_TABLE(families, '$[*]' COLUMNS(id FOR ORDINALITY, father VARCHAR(30) PATH '$.father', married INTEGER EXISTS PATH '$.marriage_date', NESTED PATH '$.children[*]' COLUMNS(child_id FOR ORDINALITY, child VARCHAR(30) PATH '$.name', age INTEGER PATH '$.age')));

我们使用嵌套路径来指定应该从子数组中提取哪些值。

这个示例还表明，我们可以通过指定序数而不是路径来为行分配id。我们还可以使用EXISTS PATH检查路径是否存在。这里，如果找到结婚日期，则married为1，否则为0。

 

4 对JSON数据进行SQL聚合 一旦我们使用JSON_TABLE将JSON数据转换为关系表，我们就可以利用SQL聚合来计算JSON数据的计数、总和、平均值等。

使用上面提供的例子，这个查询将计算每个家庭孩子的平均年龄:

SELECT father, COUNT(*) "#children", AVG(age) "age average" FROM t, JSON_TABLE(families, '$[*]', COLUMNS(id FOR ORDINALITY, father VARCHAR(30) PATH '$.father', NESTED PATH '$.children[*]' COLUMNS(age INTEGER PATH '$.age'))) fam GROUP BY id, father; ATHER #CHILDREN AGE AVERAGE John 2 11.0000 Paul 3 4.3333

这里，我们将现有的family对象与计算后的总和和平均值构造的对象合并。

 

5 结论 在这里展示了如何使用JSON_TABLE对JSON数据执行关系操作，同时也可以利用SQL的强大功能。